Macros and hidden worksheets

[Monomeeth]

Hello.

I have a workbook which contains 7 worksheets. The first six need to be
hidden so as not to confuse our users. The 7th worksheet contains a menu with
51 options, 11 of which run macros. These macros involve copying and pasting
data from the other worksheets. Everything works fine, except when the other
worksheets are hidden.

How do I get around this problem in the macro code?

Below is a sample of the code I'm using to select one of the other worksheets:

Sheets("Line Item Tracker").Select

Works fine when "Line Item Tracker" is not hidden. But when it is I get
errors.

Your help would be most appreciated!

Thanks,

Joe.

 

[FSt1]

hi
you can't select a hiden sheet.
once you select something, it becomes the active whatever (sheet, cell,
other) and a hidden sheet cann't be the active sheet.
you can extract data from a hidden sheet.
assume sheet 2 is hidden.

dim r as range
Set r = Sheets("sheet2").Range("E3")
Sheets("sheet1").Range("H1") = r.Value

or.......

Dim r As Range
Set r = Sheets("sheet2").Range("E3")
r.Copy Destination:=Sheets("sheet1").Range("F1")

or simply.....
Sheets("sheet1").Range("H1") = _
Sheets("sheet2").Range("E3").Value

number of ways to do it. seldom do you "need" to do any selecting.
but you mention " 51 options, 11 of which run macros " so i am sure swiching
over would cause a considerable re-write which may not be an desireable
option.
the only other option i see is to un-hide the sheet to do your selecting and
coping.

sheets("sheet2").visible = true ' to unhide
Sheets("sheet2").Visible = False ' to rehide

not sure if this really helped but hope so.

regards
FSt1

 

[Monomeeth]

Thanks very much FSt1, I'll play around with the "unhude" and "rehide"
options. Main reason being is that I need to run one of 11 different filters
prior to copying and pasting the data (hence the 11 different menu/macro
options).

Thanks again fopr your help.