Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

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!

Check out our Excel VBA Resources

Re: 3-fold macro problem

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.