Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and hidden worksheets
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. -- If you can measure it, you can improve it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and hidden worksheets
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" wrote: 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. -- If you can measure it, you can improve it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and hidden worksheets
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. -- If you can measure it, you can improve it! "FSt1" wrote: 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" wrote: 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. -- If you can measure it, you can improve it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
hidden macros? | Excel Programming | |||
Where are the hidden macros? | Excel Programming | |||
Unhide Worksheets (hidden via macros) | Excel Discussion (Misc queries) | |||
Hidden? macros | Excel Programming |