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.

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.

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.

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

:confused:

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

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

:confused:

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