A couple of things I would like to add to this macro is a prompt at the
beginning that says quot;Please enter hours and miles firstquot; True = continue
False = end
The second thing is on the Workbooks.Open command. Is there a way to have
all links and formulas to update and save without opening? If not I would
like a user prompt for each number or better yet can it look at a list of
tech numbers and match them to say quot;R2C3:R200C3quot; like you would write an
Index and Match formula in Excel?
ActiveSheet.Unprotect
Application.Goto Reference:=quot;R2C9:R200C15quot;
Selection.ClearContents
Application.Goto Reference:=quot;R2C24:R200C28quot;
Selection.ClearContents
Application.Goto Reference:=quot;R2C35:R200C39quot;
Selection.ClearContents
Range(quot;A2quot;).Select
Workbooks.Open (quot;C:\IMPORT.XLSquot;)
Application.Goto Reference:=quot;R200C15quot;
ActiveCell.FormulaR1C1 = quot; quot;
Application.Goto Reference:=quot;R200C16quot;
ActiveCell.FormulaR1C1 = quot; quot;
Application.Goto Reference:=quot;R2C1:R200C1quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C1:R200C1quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C3:R200C3quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C2:R200C2quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C4:R200C4quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C3:R200C3quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C5:R200C5quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C4:R200C4quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C6:R200C6quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C5:R200C5quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C8:R200C8quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C6:R200C6quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C9:R200C9quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C7:R200C7quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C10:R200C10quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C8:R200C8quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C11:R200C11quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C9:R200C9quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.TextToColumns Destination:=Range(quot;I2quot;), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
quot;-quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C16:R200C16quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C24:R200C24quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
Selection.TextToColumns Destination:=Range(quot;X2quot;), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Windows(quot;IMPORT.XLSquot;).Activate
Application.Goto Reference:=quot;R2C15:R200C15quot;
Selection.Copy
Windows(quot;1DLSUNDAY.XLSquot;).Activate
Application.Goto Reference:=quot;R2C35:R200C35quot;
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
Selection.TextToColumns Destination:=Range(quot;IA2quot;),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range(quot;A2quot;).Select
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\WEEKLY TOTALS NEWquot;)
Calculate
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9501.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9502.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9503.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9504.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9505.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9506.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9507.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9508.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9509.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9510.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9511.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9512.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9513.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9514.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9515.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9516.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9517.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9518.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9519.XLSquot;)
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\9520.XLSquot;)
Calculate
For Each w In Workbooks
If w.Name lt;gt; ThisWorkbook.Name Then
w.Close savechanges:=True
End If
Next w
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True
Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\WEEKLY TOTALS NEWquot;)
End Subans = Msgbox(quot;Please enter hours and miles firstquot;, _
vbYesNo,quot;Hours and Milesquot;)
if ans = vbNo then
exit sub
end ifalthough it seems the question should be quot;Are hours and miles entered?quot;
I don't understand your second question.
--
Regards,
Tom Ogilvy
quot;Tomkat743quot; wrote:
gt; A couple of things I would like to add to this macro is a prompt at the
gt; beginning that says quot;Please enter hours and miles firstquot; True = continue
gt; False = end
gt;
gt; The second thing is on the Workbooks.Open command. Is there a way to have
gt; all links and formulas to update and save without opening? If not I would
gt; like a user prompt for each number or better yet can it look at a list of
gt; tech numbers and match them to say quot;R2C3:R200C3quot; like you would write an
gt; Index and Match formula in Excel?
gt;
gt; ActiveSheet.Unprotect
gt; Application.Goto Reference:=quot;R2C9:R200C15quot;
gt; Selection.ClearContents
gt; Application.Goto Reference:=quot;R2C24:R200C28quot;
gt; Selection.ClearContents
gt; Application.Goto Reference:=quot;R2C35:R200C39quot;
gt; Selection.ClearContents
gt; Range(quot;A2quot;).Select
gt; Workbooks.Open (quot;C:\IMPORT.XLSquot;)
gt; Application.Goto Reference:=quot;R200C15quot;
gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; Application.Goto Reference:=quot;R200C16quot;
gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C2:R200C2quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C7:R200C7quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C10:R200C10quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C11:R200C11quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=False, Transpose:=False
gt; Selection.TextToColumns Destination:=Range(quot;I2quot;), DataType:=xlDelimited, _
gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; quot;-quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1)),
gt; TrailingMinusNumbers:=True
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C16:R200C16quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C24:R200C24quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=True, Transpose:=False
gt; Selection.TextToColumns Destination:=Range(quot;X2quot;), DataType:=xlDelimited, _
gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C15:R200C15quot;
gt; Selection.Copy
gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; Application.Goto Reference:=quot;R2C35:R200C35quot;
gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; SkipBlanks:=True, Transpose:=False
gt; Selection.TextToColumns Destination:=Range(quot;IA2quot;),
gt; DataType:=xlDelimited, _
gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; Range(quot;A2quot;).Select
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; Calculate
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9501.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9502.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9503.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9504.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9505.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9506.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9507.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9508.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9509.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9510.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9511.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9512.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9513.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9514.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9515.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9516.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9517.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9518.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9519.XLSquot;)
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\9520.XLSquot;)
gt; Calculate
gt; For Each w In Workbooks
gt; If w.Name lt;gt; ThisWorkbook.Name Then
gt; w.Close savechanges:=True
gt; End If
gt; Next w
gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; Scenarios:=True _
gt; , AllowFiltering:=True
gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; End Sub
gt;
Thank you for that message box and the code for my mileage cap. If you look
toward the end of my macro I open a workbook for each Tech ie; 9501, 9502 etc.
I only need to open them to update the information if that tech worked that
day the macro you are looking at is for Sunday I have one for each day of the
week there is a column in 1DLSunday for the tech number. If the
workbooks.open could refer to this column and only open the files for techs
that worked that day it would save some time when i get up to 30 or 40 techs
with 200 to 300 jobs. I've already done an Index, Match formula to pull that
techs jobs out and put them on his own sheet which is where his workbook
pulls all of its info from each day and compiles it into a week and then that
file updates a payroll workbook.
quot;Tom Ogilvyquot; wrote:
gt; ans = Msgbox(quot;Please enter hours and miles firstquot;, _
gt; vbYesNo,quot;Hours and Milesquot;)
gt; if ans = vbNo then
gt; exit sub
gt; end if
gt;
gt;
gt; although it seems the question should be quot;Are hours and miles entered?quot;
gt;
gt; I don't understand your second question.
gt;
gt; --
gt; Regards,
gt; Tom Ogilvy
gt;
gt; quot;Tomkat743quot; wrote:
gt;
gt; gt; A couple of things I would like to add to this macro is a prompt at the
gt; gt; beginning that says quot;Please enter hours and miles firstquot; True = continue
gt; gt; False = end
gt; gt;
gt; gt; The second thing is on the Workbooks.Open command. Is there a way to have
gt; gt; all links and formulas to update and save without opening? If not I would
gt; gt; like a user prompt for each number or better yet can it look at a list of
gt; gt; tech numbers and match them to say quot;R2C3:R200C3quot; like you would write an
gt; gt; Index and Match formula in Excel?
gt; gt;
gt; gt; ActiveSheet.Unprotect
gt; gt; Application.Goto Reference:=quot;R2C9:R200C15quot;
gt; gt; Selection.ClearContents
gt; gt; Application.Goto Reference:=quot;R2C24:R200C28quot;
gt; gt; Selection.ClearContents
gt; gt; Application.Goto Reference:=quot;R2C35:R200C39quot;
gt; gt; Selection.ClearContents
gt; gt; Range(quot;A2quot;).Select
gt; gt; Workbooks.Open (quot;C:\IMPORT.XLSquot;)
gt; gt; Application.Goto Reference:=quot;R200C15quot;
gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; Application.Goto Reference:=quot;R200C16quot;
gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C2:R200C2quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C7:R200C7quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C10:R200C10quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C11:R200C11quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; Selection.TextToColumns Destination:=Range(quot;I2quot;), DataType:=xlDelimited, _
gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; quot;-quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1)),
gt; gt; TrailingMinusNumbers:=True
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C16:R200C16quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C24:R200C24quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; Selection.TextToColumns Destination:=Range(quot;X2quot;), DataType:=xlDelimited, _
gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C15:R200C15quot;
gt; gt; Selection.Copy
gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; Application.Goto Reference:=quot;R2C35:R200C35quot;
gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; Selection.TextToColumns Destination:=Range(quot;IA2quot;),
gt; gt; DataType:=xlDelimited, _
gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; Range(quot;A2quot;).Select
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; Calculate
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9501.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9502.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9503.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9504.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9505.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9506.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9507.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9508.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9509.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9510.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9511.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9512.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9513.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9514.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9515.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9516.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9517.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9518.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9519.XLSquot;)
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\9520.XLSquot;)
gt; gt; Calculate
gt; gt; For Each w In Workbooks
gt; gt; If w.Name lt;gt; ThisWorkbook.Name Then
gt; gt; w.Close savechanges:=True
gt; gt; End If
gt; gt; Next w
gt; gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; gt; Scenarios:=True _
gt; gt; , AllowFiltering:=True
gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; End Sub
gt; gt;
Change workbook, sheet names and cell locations to reflect your actual setup.
With Workbooks(quot;1DLSUNDAY.XLSquot;).worksheets(quot;TechListquot;)
set rng = .Range(.Cells(1,quot;Equot;),.Cells(1,quot;Equot;).end(xldown))
End with
for each cell in rng
workbooks.Open quot;C:\Documents andquot; amp;_
quot; Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\quot; amp; _
cell.Value amp; quot;.xlsquot;
Next
--
Regards,
Tom Ogilvy
Tom Ogilvy
quot;Tomkat743quot; wrote:
gt; Thank you for that message box and the code for my mileage cap. If you look
gt; toward the end of my macro I open a workbook for each Tech ie; 9501, 9502 etc.
gt; I only need to open them to update the information if that tech worked that
gt; day the macro you are looking at is for Sunday I have one for each day of the
gt; week there is a column in 1DLSunday for the tech number. If the
gt; workbooks.open could refer to this column and only open the files for techs
gt; that worked that day it would save some time when i get up to 30 or 40 techs
gt; with 200 to 300 jobs. I've already done an Index, Match formula to pull that
gt; techs jobs out and put them on his own sheet which is where his workbook
gt; pulls all of its info from each day and compiles it into a week and then that
gt; file updates a payroll workbook.
gt;
gt; quot;Tom Ogilvyquot; wrote:
gt;
gt; gt; ans = Msgbox(quot;Please enter hours and miles firstquot;, _
gt; gt; vbYesNo,quot;Hours and Milesquot;)
gt; gt; if ans = vbNo then
gt; gt; exit sub
gt; gt; end if
gt; gt;
gt; gt;
gt; gt; although it seems the question should be quot;Are hours and miles entered?quot;
gt; gt;
gt; gt; I don't understand your second question.
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt; Tom Ogilvy
gt; gt;
gt; gt; quot;Tomkat743quot; wrote:
gt; gt;
gt; gt; gt; A couple of things I would like to add to this macro is a prompt at the
gt; gt; gt; beginning that says quot;Please enter hours and miles firstquot; True = continue
gt; gt; gt; False = end
gt; gt; gt;
gt; gt; gt; The second thing is on the Workbooks.Open command. Is there a way to have
gt; gt; gt; all links and formulas to update and save without opening? If not I would
gt; gt; gt; like a user prompt for each number or better yet can it look at a list of
gt; gt; gt; tech numbers and match them to say quot;R2C3:R200C3quot; like you would write an
gt; gt; gt; Index and Match formula in Excel?
gt; gt; gt;
gt; gt; gt; ActiveSheet.Unprotect
gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C15quot;
gt; gt; gt; Selection.ClearContents
gt; gt; gt; Application.Goto Reference:=quot;R2C24:R200C28quot;
gt; gt; gt; Selection.ClearContents
gt; gt; gt; Application.Goto Reference:=quot;R2C35:R200C39quot;
gt; gt; gt; Selection.ClearContents
gt; gt; gt; Range(quot;A2quot;).Select
gt; gt; gt; Workbooks.Open (quot;C:\IMPORT.XLSquot;)
gt; gt; gt; Application.Goto Reference:=quot;R200C15quot;
gt; gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; gt; Application.Goto Reference:=quot;R200C16quot;
gt; gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C2:R200C2quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C7:R200C7quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C10:R200C10quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C11:R200C11quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;I2quot;), DataType:=xlDelimited, _
gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; quot;-quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1)),
gt; gt; gt; TrailingMinusNumbers:=True
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C16:R200C16quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C24:R200C24quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;X2quot;), DataType:=xlDelimited, _
gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C15:R200C15quot;
gt; gt; gt; Selection.Copy
gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; Application.Goto Reference:=quot;R2C35:R200C35quot;
gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;IA2quot;),
gt; gt; gt; DataType:=xlDelimited, _
gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; gt; Range(quot;A2quot;).Select
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; gt; Calculate
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9501.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9502.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9503.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9504.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9505.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9506.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9507.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9508.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9509.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9510.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9511.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9512.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9513.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9514.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9515.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9516.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9517.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9518.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9519.XLSquot;)
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\9520.XLSquot;)
gt; gt; gt; Calculate
gt; gt; gt; For Each w In Workbooks
gt; gt; gt; If w.Name lt;gt; ThisWorkbook.Name Then
gt; gt; gt; w.Close savechanges:=True
gt; gt; gt; End If
gt; gt; gt; Next w
gt; gt; gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; gt; gt; Scenarios:=True _
gt; gt; gt; , AllowFiltering:=True
gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; gt; End Sub
gt; gt; gt;
Just looking at it will it try to open say 9501's workbook once for every
instance of 9501 in column C because it has multiple entries of 9501 (one for
each job completed that day) also I don't really understand the .Cells
relationship , is (1,quot;Equot;) refering to row 1, column 5?
quot;Tom Ogilvyquot; wrote:
gt; Change workbook, sheet names and cell locations to reflect your actual setup.
gt;
gt; With Workbooks(quot;1DLSUNDAY.XLSquot;).worksheets(quot;TechListquot;)
gt; set rng = .Range(.Cells(1,quot;Equot;),.Cells(1,quot;Equot;).end(xldown))
gt; End with
gt;
gt; for each cell in rng
gt; workbooks.Open quot;C:\Documents andquot; amp;_
gt; quot; Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\quot; amp; _
gt; cell.Value amp; quot;.xlsquot;
gt; Next
gt;
gt; --
gt; Regards,
gt; Tom Ogilvy
gt;
gt; Tom Ogilvy
gt;
gt;
gt;
gt; quot;Tomkat743quot; wrote:
gt;
gt; gt; Thank you for that message box and the code for my mileage cap. If you look
gt; gt; toward the end of my macro I open a workbook for each Tech ie; 9501, 9502 etc.
gt; gt; I only need to open them to update the information if that tech worked that
gt; gt; day the macro you are looking at is for Sunday I have one for each day of the
gt; gt; week there is a column in 1DLSunday for the tech number. If the
gt; gt; workbooks.open could refer to this column and only open the files for techs
gt; gt; that worked that day it would save some time when i get up to 30 or 40 techs
gt; gt; with 200 to 300 jobs. I've already done an Index, Match formula to pull that
gt; gt; techs jobs out and put them on his own sheet which is where his workbook
gt; gt; pulls all of its info from each day and compiles it into a week and then that
gt; gt; file updates a payroll workbook.
gt; gt;
gt; gt; quot;Tom Ogilvyquot; wrote:
gt; gt;
gt; gt; gt; ans = Msgbox(quot;Please enter hours and miles firstquot;, _
gt; gt; gt; vbYesNo,quot;Hours and Milesquot;)
gt; gt; gt; if ans = vbNo then
gt; gt; gt; exit sub
gt; gt; gt; end if
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; although it seems the question should be quot;Are hours and miles entered?quot;
gt; gt; gt;
gt; gt; gt; I don't understand your second question.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Regards,
gt; gt; gt; Tom Ogilvy
gt; gt; gt;
gt; gt; gt; quot;Tomkat743quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; A couple of things I would like to add to this macro is a prompt at the
gt; gt; gt; gt; beginning that says quot;Please enter hours and miles firstquot; True = continue
gt; gt; gt; gt; False = end
gt; gt; gt; gt;
gt; gt; gt; gt; The second thing is on the Workbooks.Open command. Is there a way to have
gt; gt; gt; gt; all links and formulas to update and save without opening? If not I would
gt; gt; gt; gt; like a user prompt for each number or better yet can it look at a list of
gt; gt; gt; gt; tech numbers and match them to say quot;R2C3:R200C3quot; like you would write an
gt; gt; gt; gt; Index and Match formula in Excel?
gt; gt; gt; gt;
gt; gt; gt; gt; ActiveSheet.Unprotect
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C15quot;
gt; gt; gt; gt; Selection.ClearContents
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C24:R200C28quot;
gt; gt; gt; gt; Selection.ClearContents
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C35:R200C39quot;
gt; gt; gt; gt; Selection.ClearContents
gt; gt; gt; gt; Range(quot;A2quot;).Select
gt; gt; gt; gt; Workbooks.Open (quot;C:\IMPORT.XLSquot;)
gt; gt; gt; gt; Application.Goto Reference:=quot;R200C15quot;
gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; gt; gt; Application.Goto Reference:=quot;R200C16quot;
gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C2:R200C2quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C7:R200C7quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C10:R200C10quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C11:R200C11quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;I2quot;), DataType:=xlDelimited, _
gt; gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; gt; quot;-quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1)),
gt; gt; gt; gt; TrailingMinusNumbers:=True
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C16:R200C16quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C24:R200C24quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;X2quot;), DataType:=xlDelimited, _
gt; gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C15:R200C15quot;
gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; Application.Goto Reference:=quot;R2C35:R200C35quot;
gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;IA2quot;),
gt; gt; gt; gt; DataType:=xlDelimited, _
gt; gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; gt; gt; Range(quot;A2quot;).Select
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; gt; gt; Calculate
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9501.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9502.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9503.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9504.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9505.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9506.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9507.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9508.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9509.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9510.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9511.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9512.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9513.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9514.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9515.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9516.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9517.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9518.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9519.XLSquot;)
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\9520.XLSquot;)
gt; gt; gt; gt; Calculate
gt; gt; gt; gt; For Each w In Workbooks
gt; gt; gt; gt; If w.Name lt;gt; ThisWorkbook.Name Then
gt; gt; gt; gt; w.Close savechanges:=True
gt; gt; gt; gt; End If
gt; gt; gt; gt; Next w
gt; gt; gt; gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; gt; gt; gt; Scenarios:=True _
gt; gt; gt; gt; , AllowFiltering:=True
gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
cells is indexed with row, then column. so yes, cells(1,quot;Equot;) is E1. You
can also use Cells(1,5)
Because there are duplicates, you can just ignore the error of attempting to
open a workbook twice:
With Workbooks(quot;1DLSUNDAY.XLSquot;).worksheets(quot;TechListquot;)
set rng = .Range(.Cells(1,quot;Equot;),.Cells(1,quot;Equot;).end(xldown))
End with
On Error Resume Next
for each cell in rng
workbooks.Open quot;C:\Documents andquot; amp;_
quot; Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\quot; amp; _
cell.Value amp; quot;.xlsquot;
Next
On Error goto 0
or we could build a collection and avoid the error:
Dim bkList as New Collection
With Workbooks(quot;1DLSUNDAY.XLSquot;).worksheets(quot;TechListquot;)
set rng = .Range(.Cells(1,quot;Equot;),.Cells(1,quot;Equot;).end(xldown))
End with
On error Resume Next
for each cell in rng
bklist.Add trim(cell.Text), trim(cell.Text)
Next
On Error goto 0
for each itm in collection
workbooks.Open quot;C:\Documents andquot; amp;_
quot; Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\quot; amp; _
itm amp; quot;.xlsquot;
Next
--
Regards,
Tom Ogilvy
quot;Tomkat743quot; wrote:
gt; Just looking at it will it try to open say 9501's workbook once for every
gt; instance of 9501 in column C because it has multiple entries of 9501 (one for
gt; each job completed that day) also I don't really understand the .Cells
gt; relationship , is (1,quot;Equot;) refering to row 1, column 5?
gt;
gt; quot;Tom Ogilvyquot; wrote:
gt;
gt; gt; Change workbook, sheet names and cell locations to reflect your actual setup.
gt; gt;
gt; gt; With Workbooks(quot;1DLSUNDAY.XLSquot;).worksheets(quot;TechListquot;)
gt; gt; set rng = .Range(.Cells(1,quot;Equot;),.Cells(1,quot;Equot;).end(xldown))
gt; gt; End with
gt; gt;
gt; gt; for each cell in rng
gt; gt; workbooks.Open quot;C:\Documents andquot; amp;_
gt; gt; quot; Settings\Tom\Desktop\CHARTER BLANK\NEW BLANK\quot; amp; _
gt; gt; cell.Value amp; quot;.xlsquot;
gt; gt; Next
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt; Tom Ogilvy
gt; gt;
gt; gt; Tom Ogilvy
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Tomkat743quot; wrote:
gt; gt;
gt; gt; gt; Thank you for that message box and the code for my mileage cap. If you look
gt; gt; gt; toward the end of my macro I open a workbook for each Tech ie; 9501, 9502 etc.
gt; gt; gt; I only need to open them to update the information if that tech worked that
gt; gt; gt; day the macro you are looking at is for Sunday I have one for each day of the
gt; gt; gt; week there is a column in 1DLSunday for the tech number. If the
gt; gt; gt; workbooks.open could refer to this column and only open the files for techs
gt; gt; gt; that worked that day it would save some time when i get up to 30 or 40 techs
gt; gt; gt; with 200 to 300 jobs. I've already done an Index, Match formula to pull that
gt; gt; gt; techs jobs out and put them on his own sheet which is where his workbook
gt; gt; gt; pulls all of its info from each day and compiles it into a week and then that
gt; gt; gt; file updates a payroll workbook.
gt; gt; gt;
gt; gt; gt; quot;Tom Ogilvyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; ans = Msgbox(quot;Please enter hours and miles firstquot;, _
gt; gt; gt; gt; vbYesNo,quot;Hours and Milesquot;)
gt; gt; gt; gt; if ans = vbNo then
gt; gt; gt; gt; exit sub
gt; gt; gt; gt; end if
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; although it seems the question should be quot;Are hours and miles entered?quot;
gt; gt; gt; gt;
gt; gt; gt; gt; I don't understand your second question.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Tom Ogilvy
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Tomkat743quot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; A couple of things I would like to add to this macro is a prompt at the
gt; gt; gt; gt; gt; beginning that says quot;Please enter hours and miles firstquot; True = continue
gt; gt; gt; gt; gt; False = end
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The second thing is on the Workbooks.Open command. Is there a way to have
gt; gt; gt; gt; gt; all links and formulas to update and save without opening? If not I would
gt; gt; gt; gt; gt; like a user prompt for each number or better yet can it look at a list of
gt; gt; gt; gt; gt; tech numbers and match them to say quot;R2C3:R200C3quot; like you would write an
gt; gt; gt; gt; gt; Index and Match formula in Excel?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ActiveSheet.Unprotect
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C15quot;
gt; gt; gt; gt; gt; Selection.ClearContents
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C24:R200C28quot;
gt; gt; gt; gt; gt; Selection.ClearContents
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C35:R200C39quot;
gt; gt; gt; gt; gt; Selection.ClearContents
gt; gt; gt; gt; gt; Range(quot;A2quot;).Select
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\IMPORT.XLSquot;)
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R200C15quot;
gt; gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R200C16quot;
gt; gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot; quot;
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C1:R200C1quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C2:R200C2quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C3:R200C3quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C4:R200C4quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C5:R200C5quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C6:R200C6quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C7:R200C7quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C10:R200C10quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C8:R200C8quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C11:R200C11quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C9:R200C9quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=False, Transpose:=False
gt; gt; gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;I2quot;), DataType:=xlDelimited, _
gt; gt; gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; gt; gt; quot;-quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1)),
gt; gt; gt; gt; gt; TrailingMinusNumbers:=True
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C16:R200C16quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C24:R200C24quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;X2quot;), DataType:=xlDelimited, _
gt; gt; gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; gt; gt; gt; Windows(quot;IMPORT.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C15:R200C15quot;
gt; gt; gt; gt; gt; Selection.Copy
gt; gt; gt; gt; gt; Windows(quot;1DLSUNDAY.XLSquot;).Activate
gt; gt; gt; gt; gt; Application.Goto Reference:=quot;R2C35:R200C35quot;
gt; gt; gt; gt; gt; Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
gt; gt; gt; gt; gt; SkipBlanks:=True, Transpose:=False
gt; gt; gt; gt; gt; Selection.TextToColumns Destination:=Range(quot;IA2quot;),
gt; gt; gt; gt; gt; DataType:=xlDelimited, _
gt; gt; gt; gt; gt; TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
gt; gt; gt; gt; gt; Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
gt; gt; gt; gt; gt; quot;-quot;, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
gt; gt; gt; gt; gt; Range(quot;A2quot;).Select
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; gt; gt; gt; Calculate
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9501.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9502.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9503.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9504.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9505.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9506.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9507.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9508.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9509.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9510.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9511.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9512.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9513.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9514.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9515.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9516.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9517.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9518.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9519.XLSquot;)
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\9520.XLSquot;)
gt; gt; gt; gt; gt; Calculate
gt; gt; gt; gt; gt; For Each w In Workbooks
gt; gt; gt; gt; gt; If w.Name lt;gt; ThisWorkbook.Name Then
gt; gt; gt; gt; gt; w.Close savechanges:=True
gt; gt; gt; gt; gt; End If
gt; gt; gt; gt; gt; Next w
gt; gt; gt; gt; gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; gt; gt; gt; gt; Scenarios:=True _
gt; gt; gt; gt; gt; , AllowFiltering:=True
gt; gt; gt; gt; gt; Workbooks.Open (quot;C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
gt; gt; gt; gt; gt; BLANK\WEEKLY TOTALS NEWquot;)
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
留言列表
