When I tried this method, I received a number of errors saying that arguments
in all the subs within the module were not optional... so I made the argument
for all subs (z as string)... then I received an error for the calling of the
main sub for the button.

"Tushar Mehta" wrote:

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?