Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hidden Worksheet
I have a macro which depends on getting the name of a worksheet:
Worksheets(sht & i).Select where sht is a string and i an integer. This macro runs fine when the worksheet selected is visible. However, should I change the visible status to "xlSheetHidden", the worksheet doesn't work anymore. Is there anyway to make the macro work even when the worksheet is hidden? TIA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hidden Worksheet
You can't select a hidden worksheet, but you can work with it. Just forget
about the Select stuff and do whatever you need to do; e.g.: Worksheets(sht & i).Range("A1").Copy -- Vasant "Shatin" wrote in message ... I have a macro which depends on getting the name of a worksheet: Worksheets(sht & i).Select where sht is a string and i an integer. This macro runs fine when the worksheet selected is visible. However, should I change the visible status to "xlSheetHidden", the worksheet doesn't work anymore. Is there anyway to make the macro work even when the worksheet is hidden? TIA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hidden Worksheet
Thanks for reply, Vasant. The problem is the macro is a long one with a few
QueryTables. So I am a bit at a loss as to what to do if I can't select the worksheet first and instead have to add Worksheets(sht & i) all over the place. "Vasant Nanavati" <vasantn AT aol DOT com wrote in message ... You can't select a hidden worksheet, but you can work with it. Just forget about the Select stuff and do whatever you need to do; e.g.: Worksheets(sht & i).Range("A1").Copy -- Vasant "Shatin" wrote in message ... I have a macro which depends on getting the name of a worksheet: Worksheets(sht & i).Select where sht is a string and i an integer. This macro runs fine when the worksheet selected is visible. However, should I change the visible status to "xlSheetHidden", the worksheet doesn't work anymore. Is there anyway to make the macro work even when the worksheet is hidden? TIA. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hidden Worksheet
Hi Shatin! Some things can't be done to a hidden sheet (e.g. selecting). :( If you really have to manipulate the hidden sheet, maybe you can unhide it, then hide it again after. TheHiddenSheet.Visible = xlSheetVisible 'do your stuff here... TheHiddenSheet.Visible = xlSheetHidden Shatin Wrote: Thanks for reply, Vasant. The problem is the macro is a long one with a few QueryTables. So I am a bit at a loss as to what to do if I can't select the worksheet first and instead have to add Worksheets(sht & i) all over the place. "Vasant Nanavati" <vasantn AT aol DOT com wrote in message ... You can't select a hidden worksheet, but you can work with it. Just forget about the Select stuff and do whatever you need to do; e.g.: Worksheets(sht & i).Range("A1").Copy -- Vasant "Shatin" wrote in message ... I have a macro which depends on getting the name of a worksheet: Worksheets(sht & i).Select where sht is a string and i an integer. This macro runs fine when the worksheet selected is visible. However, should I change the visible status to "xlSheetHidden", the worksheet doesn't work anymore. Is there anyway to make the macro work even when the worksheet is hidden? TIA. -- T-®ex ------------------------------------------------------------------------ T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=401655 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Hidden Worksheet
Right-O ... and with Application.ScreenUpdating set to false, who's the
wiser ;-) "T-®ex" wrote in message ... Hi Shatin! Some things can't be done to a hidden sheet (e.g. selecting). :( If you really have to manipulate the hidden sheet, maybe you can unhide it, then hide it again after. TheHiddenSheet.Visible = xlSheetVisible 'do your stuff here... TheHiddenSheet.Visible = xlSheetHidden Shatin Wrote: Thanks for reply, Vasant. The problem is the macro is a long one with a few QueryTables. So I am a bit at a loss as to what to do if I can't select the worksheet first and instead have to add Worksheets(sht & i) all over the place. "Vasant Nanavati" <vasantn AT aol DOT com wrote in message ... You can't select a hidden worksheet, but you can work with it. Just forget about the Select stuff and do whatever you need to do; e.g.: Worksheets(sht & i).Range("A1").Copy -- Vasant "Shatin" wrote in message ... I have a macro which depends on getting the name of a worksheet: Worksheets(sht & i).Select where sht is a string and i an integer. This macro runs fine when the worksheet selected is visible. However, should I change the visible status to "xlSheetHidden", the worksheet doesn't work anymore. Is there anyway to make the macro work even when the worksheet is hidden? TIA. -- T-®ex ------------------------------------------------------------------------ T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=401655 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem - hidden cell names in worksheet | Excel Worksheet Functions | |||
2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
Hidden Columns No Longer Hidden after Copying Worksheet? | Excel Discussion (Misc queries) | |||
How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming |