3-fold macro problem

Posted by Jim on April 19, 2001 12:38 PM

Please help with the VB code for the following: In the middle of a large macro, I want to
1) retrieve the rightmost 6 characters (which will actually be numbers) of the active worksheet tab name
2) insert those 6 characters (xxxxxx) as part of the filename and worksheet tab name in a VLOOKUP that gets placed into the active cell (i.e. =VLOOKUP("Totals",[xxxxxx.xls]DEPxxxxxx!$A$1:$C$100,2,0)
3) convert the result into a value instead of the VLOOKUP formula
I greatly appreciate any help!

Posted by Dax on April 19, 2001 1:06 PM

How's about this?

Sub YourMacro()
Dim SixChars As String, Ref As String

SixChars = Right$(ActiveSheet.Name, 6)
Ref = "[" & SixChars & ".xls]DEP" & SixChars & "!$A$1:$C$100"
ActiveCell.Formula = "=VLOOKUP(""Totals""," & Ref & ",2,false)"
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues
End Sub

Hope this helps,
Dax.