I have a workbook that has many worksheets. Macros are
attached to each of these that extract data to a temporary
worksheet, save the file as a csv, clears the temp file
then returns to the worksheet it came from. During
execution the temp file becomes the active worksheet so
the user can see the data that is being exported

The problem is that the names of the worksheets are
written within the macros and if someone changes the name
of the sheet, it exports the data but gets hung up during
exit since it cannot find the worksheet to return to.

How can I store the name of a sheet as a variable to be
used during macro execution, so the user returns to the
sheet they initiated the macro from no matter what they
may have renamed the sheet to? I would like to eliminate
the sheet names from the code so the user can rename them
as necessary.

Any help is appreciated.

Lee

Lee,

Use the worksheet codename rather than it's Excel name. This is of the
format

Sheet1.Activate

rather than Worksheets("Sheet1").Activate

The codenmae is the first name in the name pairs in the VBE explorer window.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Lee" wrote in message
...
I have a workbook that has many worksheets. Macros are
attached to each of these that extract data to a temporary
worksheet, save the file as a csv, clears the temp file
then returns to the worksheet it came from. During
execution the temp file becomes the active worksheet so
the user can see the data that is being exported

The problem is that the names of the worksheets are
written within the macros and if someone changes the name
of the sheet, it exports the data but gets hung up during
exit since it cannot find the worksheet to return to.

How can I store the name of a sheet as a variable to be
used during macro execution, so the user returns to the
sheet they initiated the macro from no matter what they
may have renamed the sheet to? I would like to eliminate
the sheet names from the code so the user can rename them
as necessary.

Any help is appreciated.

Lee

Bob,

Thanks !!!

Lee

-----Original Message-----
Lee,

Use the worksheet codename rather than it's Excel name.
This is of the
format

Sheet1.Activate

rather than Worksheets("Sheet1").Activate

The codenmae is the first name in the name pairs in the
VBE explorer window.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Lee" wrote in
message
...
I have a workbook that has many worksheets. Macros are
attached to each of these that extract data to a
temporary
worksheet, save the file as a csv, clears the temp file
then returns to the worksheet it came from. During
execution the temp file becomes the active worksheet so
the user can see the data that is being exported

The problem is that the names of the worksheets are
written within the macros and if someone changes the
name
of the sheet, it exports the data but gets hung up
during
exit since it cannot find the worksheet to return to.

How can I store the name of a sheet as a variable to be
used during macro execution, so the user returns to the
sheet they initiated the macro from no matter what they
may have renamed the sheet to? I would like to
eliminate
the sheet names from the code so the user can rename
them
as necessary.

Any help is appreciated.

Lee


.

Dim sh as Worksheet
set sh = Activesheet




sh.Activate

--
Regards,
Tom Ogilvy

"Lee" wrote in message
...
I have a workbook that has many worksheets. Macros are
attached to each of these that extract data to a temporary
worksheet, save the file as a csv, clears the temp file
then returns to the worksheet it came from. During
execution the temp file becomes the active worksheet so
the user can see the data that is being exported

The problem is that the names of the worksheets are
written within the macros and if someone changes the name
of the sheet, it exports the data but gets hung up during
exit since it cannot find the worksheet to return to.

How can I store the name of a sheet as a variable to be
used during macro execution, so the user returns to the
sheet they initiated the macro from no matter what they
may have renamed the sheet to? I would like to eliminate
the sheet names from the code so the user can rename them
as necessary.

Any help is appreciated.

Lee