Problem with Hidden Worksheet

[Shatin]

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.

[Vasant Nanavati]

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" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>

[Shatin]

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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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-容x]

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

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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
>
>

[William Benson]

Right-O ... and with Application.ScreenUpdating set to false, who's the
wiser ;-)


"T-容x" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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-容x
> ------------------------------------------------------------------------
> T-容x's Profile:
> http://www.excelforum.com/member.php...o&userid=26572
> View this thread: http://www.excelforum.com/showthread...hreadid=401655
>