I'm running against this problem: a file should be run whenever people want.
however, the last part to complete the file is that the file from yesterday should be opened.
From this file data will be extracted via the VLookup formula...that's the plan.
However, with dim statements it won't work.
for now the code is:
mynum, mysearch, this1, that1, other1
'Open the file from yesterday. Define the date string of this week and last week
mysearch = Left(mynum, 8) & Right(mynum, 2) - 1 'mynum is a date string like 2008-06-26
this1 = "Outstanding invoices " & mynum & ".xls"
that1 = "Outstanding invoices " & mysearch & ".xls"
Set other1 = Workbooks(that1).Sheets("Raw").Range("comments")
ChDir "Z:FINANCEF&AAPInvoices overdue"
Workbooks.Open Filename:= _
"Z:FINANCEF&AAPInvoices overdue" & that1
'Search for comments last week from last week's file
Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).Select
Selection.FormulaR1C1 = _
Selection.Value = Selection.Value
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the "this1, that1" etc appear when you point with your mouse in the code as correct but
the VLookup function won't work: it takes a long time and when interupted the macro the cells show the VLookup formula with
"[that1]" instead of the workbook's name.
I also tried to dim that1 as workbook but that did not help.
Obviously I'm doing something wrong.
I appreciate your help with this.
Thanks in advance.