Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving sheet data to another sheet automatically | Excel Worksheet Functions | |||
Problem with Saving | Excel Discussion (Misc queries) | |||
Annoying problem saving sheet w/macros as CSV | Excel Discussion (Misc queries) | |||
macro saving problem | Excel Worksheet Functions | |||
Saving problem | Excel Programming |