Pass Z as an argument to the subsequent subs as in:
sub Main()
Z=ActiveWorkbook.Name
'...
sub2 Z
'...
end sub
sub Sub2(Z as string)
'...
Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z _
& "]Plan'!R2170C242:R2229C248,7,FALSE)"
'...
end sub
Also, note that is is almost never necessary to select/activate
worksheets/ranges. For some more on that, see
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article ,
says...
I currently have this line in the first of a series of subs.
Z = ActiveWorkbook.Name
This line is used to store the name of the workbook from which the macro is
executed -- something that is necessary, because later subs in the series
pull data from the original workbook to a new one.
However, in the later subs, where I am hoping the name would be recalled, my
current code is failing.
Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[" & Z & "]Plan'!R2170C242:R2229C248,7,FALSE)"
Is there something wrong with my syntax? Do I need to somehow declare the
variable across all the subs? How does that work?