If I understand you correctly. The macro is desinged to be run against any
sheet in the workbook. When it is runs it creates a new sheet (in the same
workbook or in a whole new workbook?) and copies some info from the original
sheet to that new sheet that you just created? There are some fairly easy
ways to do this and I am a little worried that your code is getting more
complicated that it needs to be. Post your code and lets take a look at what
you have so far.

"Mark" wrote:

The variable, Z, needs to be the name of the worksheet from which the macro
is run.

The macro runs a number of subs that first create a new sheet. Then, the
macro pulls data from the original sheet to the new sheet. Currently, I use
the actual name of the original file -- but I want the variable to be used so
that I can set it to the workbook name, no matter how the file is renamed, so
it can be called throughout the subs.

I don't know much about 'passing' variables, etc. Not sure what that entails.



"Jim Thomlinson" wrote:

Without being able to see your code and how you are passing and using your
variables it is difficult to respond directly to your question. In general
though this is how it works... Variables die with the procedure that creates
them. As soon as you hit end sub or end function the declared variables are
premanenetly destroyed. There are two ways around this. One is to declare the
variable not withing the preocedure but rather at the beginning of the
module. This variable lasts for the duration of the code execution. If it is
declared Private then it can be used by any procedure within the module. If
it is declared public then it can be used by an procedure anywhere.

public myGlobalVariable as string
private myModuleVariable as string

These kind of declarations sound really great but here is the downside...
They are a beast to debug. This is becuase what procedure had them last and
what is the current value. At any given time it can be very difficult to
know. Use these as sparingly as possible!

The other way to keep a varible from being destroued is to declare it
"Static". Whe this happens the variable persists even after the procedure
ends. If you call the procedure again then the variable is just how you left
it.

Chances are for what you want you will probably end up usisng a global
variable. If all you are doing is storing the name of the current worksheet
and are not changing it back and forth throughout the code executions this is
probably reasonable...

HTH


"Mark" wrote:

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?