Solved: VLOOKUP problem

[mugcy]

HI, I have a prob with the following


I have a workbook(cash rec.xls) with 60 different WS for each store.This file is saved in the "S:\Cash\Banking Rec 2009" folder. Each WS has a different sheet name i.e

sheet1(abc 01)
sheet2(def 02)
sheet3(ghi 03)



I have setup the following VLOOKUP statement using a macro on the first sheet.

[vba] Range("G4").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'S:\Cash\Banking Rec 2009\Store Cash Recs\[abc.xls]Period 6'!R5C2:R43C11,7,0)"
Range("G13").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'S:\Cash\Banking Rec 2009\Store Cash Recs\[abc.xls]Period 6'!R5C2:R43C11,7,0)"
Range("G23").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'S:\Cash\Banking Rec 2009\Store Cash Recs\[abc.xls]Period 6'!R5C2:R43C11,7,0)"
Range("G33").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'S:\Cash\Banking Rec 2009\Store Cash Recs\[abc.xls]Period 6'!R5C2:R43C11,7,0)"
Range("G34").Select
[/vba]
The prob I have is to get to run a macro (lets say from sheet2), so that it will automatically replace the .xls file in the VLOOKUP statement with "def" and so on for other sheets without direct input


I'm Very new to VBA. Can some one please help???

[mdmackillop]

You can pass a value, in this case the sheet name, to another sub to be incorporated into the formulas string.
[VBA]
Sub GetBookData()
GetData ActiveSheet.Name
End Sub

Sub GetData(xls As String)
Range("G4").FormulaR1C1 = _
"=VLOOKUP(RC[-6],'S:\Cash\Banking Rec 2009\Store Cash Recs\[" & xls & ".xls]Period 6'!R5C2:R43C11,7,0)"
Range("G13").FormulaR1C1 = _
"=VLOOKUP(RC[-6],'S:\Cash\Banking Rec 2009\Store Cash Recs\[" & xls & ".xls]Period 6'!R5C2:R43C11,7,0)"
'etc.
End Sub

[/VBA]

[mugcy]

Thank you Very much

It works