Problem of saving sheet name for further use
 

With a lot of help from many of you, I've created a set of spreadsheets
that use buttons/macros to take a selected county name from a summary
sheet and apply an advanced filter to background datasheets (hidden
from the end user) and create a new sheet with the filtered detailed
records. This all works great. But the number of spreadsheets is
increasing, and I'm creating another button/macro to return the user to
the original sheet they started from without having to scroll through
all the tabs (believe me, they need all the help they can get). I've
saved the original sheet name into the sheet created by the advanced
filter, but this means the Print Area includes this and prints an
unnecessary sheet. Is there another way to store the original sheet
name without using a cell, thereby changing the print area to include
it? Or someway to exclude this cell when the print area is determined?
(The length of the print area will vary depending on how many records
are extracted. and the width will vary on which datasheet they are
being filtered from.)
Any ideas?

Problem of saving sheet name for further use
 

Dave,

Have you tried using a global variable?

At the top of a codemodule, declare the variable:

Dim mySheetName As String

Then set the variable in your code:

mySheetName = Activesheet.Name

Then use it later, in another procedu

Worksheets(mySheetName).Activate

HTH,
Bernie
MS Excel MVP


"davegb" wrote in message
oups.com...
With a lot of help from many of you, I've created a set of spreadsheets
that use buttons/macros to take a selected county name from a summary
sheet and apply an advanced filter to background datasheets (hidden
from the end user) and create a new sheet with the filtered detailed
records. This all works great. But the number of spreadsheets is
increasing, and I'm creating another button/macro to return the user to
the original sheet they started from without having to scroll through
all the tabs (believe me, they need all the help they can get). I've
saved the original sheet name into the sheet created by the advanced
filter, but this means the Print Area includes this and prints an
unnecessary sheet. Is there another way to store the original sheet
name without using a cell, thereby changing the print area to include
it? Or someway to exclude this cell when the print area is determined?
(The length of the print area will vary depending on how many records
are extracted. and the width will vary on which datasheet they are
being filtered from.)
Any ideas?

Problem of saving sheet name for further use
 

Hi Dave

You can use a cell on top of your sheet and make the font white
This way you don't see it when you print it but you can use the cell.value in your macro

--
Regards Ron de Bruin
http://www.rondebruin.nl



"davegb" wrote in message oups.com...
With a lot of help from many of you, I've created a set of spreadsheets
that use buttons/macros to take a selected county name from a summary
sheet and apply an advanced filter to background datasheets (hidden
from the end user) and create a new sheet with the filtered detailed
records. This all works great. But the number of spreadsheets is
increasing, and I'm creating another button/macro to return the user to
the original sheet they started from without having to scroll through
all the tabs (believe me, they need all the help they can get). I've
saved the original sheet name into the sheet created by the advanced
filter, but this means the Print Area includes this and prints an
unnecessary sheet. Is there another way to store the original sheet
name without using a cell, thereby changing the print area to include
it? Or someway to exclude this cell when the print area is determined?
(The length of the print area will vary depending on how many records
are extracted. and the width will vary on which datasheet they are
being filtered from.)
Any ideas?

Problem of saving sheet name for further use
 

Dave,

I should have noted that the global variable will only work within a single
session: close the workbook, lose the value.

Another option would be to write the value to the registry, using the
SaveSetting and GettSetting commands.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Dave,

Have you tried using a global variable?

At the top of a codemodule, declare the variable:

Dim mySheetName As String

Then set the variable in your code:

mySheetName = Activesheet.Name

Then use it later, in another procedu

Worksheets(mySheetName).Activate

HTH,
Bernie
MS Excel MVP


"davegb" wrote in message
oups.com...
With a lot of help from many of you, I've created a set of spreadsheets
that use buttons/macros to take a selected county name from a summary
sheet and apply an advanced filter to background datasheets (hidden
from the end user) and create a new sheet with the filtered detailed
records. This all works great. But the number of spreadsheets is
increasing, and I'm creating another button/macro to return the user to
the original sheet they started from without having to scroll through
all the tabs (believe me, they need all the help they can get). I've
saved the original sheet name into the sheet created by the advanced
filter, but this means the Print Area includes this and prints an
unnecessary sheet. Is there another way to store the original sheet
name without using a cell, thereby changing the print area to include
it? Or someway to exclude this cell when the print area is determined?
(The length of the print area will vary depending on how many records
are extracted. and the width will vary on which datasheet they are
being filtered from.)
Any ideas?

Problem of saving sheet name for further use
 

Thanks for the suggestions! I've been working on other, more urgent
features of this application, and haven't had time to work on this
aspect. But I will return to it when I have some time.