S
Simon
I use the following below (The ranges set are for date stamping)
I wish values to go into MyWBAccRep to appear from MyWBSEL. However I
don't know how to refer the source workbook in the VLOOKUP function
correctly. The sheet it needs to look in is RSA and its within the
workbook MyWBSEL.
Dim MyWBAccRep As Workbook
Dim MyWBSEL As Workbook
Dim MyWBConvertor As Workbook
Set MyWBConvertor = Workbooks.Open("N:\mis\ Reporting\Latest
\AudaSourceConvertorv2.xls")
MyWBConvertor.Activate
Dim MyPath As String, MyRange As Range
Dim MyPath2 As String, MyRange2 As Range
MyPath = MyWBConvertor.Path
MyPath2 = MyWBConvertor.Path
Set MyRange = MyWBConvertor.ActiveSheet.Range("C25") ' Last month
Set MyRange2 = MyWBConvertor.ActiveSheet.Range("C26") ' Year
Set MyWBAccRep = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "AccountsReport" & " " & MyRange.Value &
MyRange2.Value & ".xls")
Set MyWBSEL = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "SEL Figures New Version" & " " & MyRange.Value
& MyRange2.Value & ".xls")
MyWBAccRep.Activate
Range("E9").FormulaR1C1 = _
"=VLOOKUP(R[-3]C, MyWBSEL!RSA!R1:R65536, 7, FALSE)"
Its this last line where I encounte problems.
I wish values to go into MyWBAccRep to appear from MyWBSEL. However I
don't know how to refer the source workbook in the VLOOKUP function
correctly. The sheet it needs to look in is RSA and its within the
workbook MyWBSEL.
Dim MyWBAccRep As Workbook
Dim MyWBSEL As Workbook
Dim MyWBConvertor As Workbook
Set MyWBConvertor = Workbooks.Open("N:\mis\ Reporting\Latest
\AudaSourceConvertorv2.xls")
MyWBConvertor.Activate
Dim MyPath As String, MyRange As Range
Dim MyPath2 As String, MyRange2 As Range
MyPath = MyWBConvertor.Path
MyPath2 = MyWBConvertor.Path
Set MyRange = MyWBConvertor.ActiveSheet.Range("C25") ' Last month
Set MyRange2 = MyWBConvertor.ActiveSheet.Range("C26") ' Year
Set MyWBAccRep = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "AccountsReport" & " " & MyRange.Value &
MyRange2.Value & ".xls")
Set MyWBSEL = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "SEL Figures New Version" & " " & MyRange.Value
& MyRange2.Value & ".xls")
MyWBAccRep.Activate
Range("E9").FormulaR1C1 = _
"=VLOOKUP(R[-3]C, MyWBSEL!RSA!R1:R65536, 7, FALSE)"
Its this last line where I encounte problems.