MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macros not working when sheets being refrenced are hidden


Posted by Randy on September 14, 2001 7:11 AM

Any ideas...I get an error code "select method of worksheet class failed"


Posted by Mark O'Brien on September 14, 2001 7:28 AM

When you hide sheets you can still refer to cells and objects on the sheet. However, when it is hidden you cannot "select" anything on that sheet because selection is a visible thing. I'm guessing that you have some code that says something like:

Sheets("Sheet1").Cells("A1").select


If yo want to post a little bit of your code then maybe I can help get around the problem.

Posted by randy on September 14, 2001 7:41 AM

some of the code i'm working on

I have refrences all over the place concerning the hidden sheets. For example these lines of code refrence sheet3 which i would like to hide

Sheets("SHEET3").Select
Range("A17").Select
Range("SHEET3!$A$16:$M$500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"SHEET3!$A$1:$A$14"), CopyToRange:=Range("SHEET3!$P$1:$AB$1"), Unique:=False
Sheets("MATERIALS (2)").Select

Posted by Mark O'Brien on September 14, 2001 7:59 AM

Re: some of the code i'm working on


OK, I'm assuming that you are basically copying data from one range on sheet3 to another range on sheet3. You can get rid of the ".select" commands and just refer to the cells using their complete address. Replace the code you plsted with this. Hopefully this should work.

Sheets("SHEET3").Range("SHEET3!$A$16:$M$500").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("SHEET3!$A$1:$A$14"), _
CopyToRange:=Range("SHEET3!$P$1:$AB$1"), _
Unique:=False

I'm guessing you used the macro recorder to record this. Basically instead of selecting the worksheet then the ranges, we're just telling excel where the ranges are and that is sufficient.

Any problems then repost.