Sheets.Select

  • Thread starter Thread starter StephanieH
  • Start date Start date
S

StephanieH

What might prevent Sheets.Select from working? I've run across the same
problem in Excel 200 and Excel 2003 in different workbooks. In each case,
the line Sheets.Select has no response (doesn't select any of the sheets in
the workbooks). There's no error so the macro continues to run.

I've also gome back and recorded an array that lists each of the sheets and
replaced the "Sheets.Select", but it does the same thing.

The odd thing is that each of these commands do work in other workbooks, so
I'm perplexed on why it works on some but not others. Anyone aware of
anything I can check?
 
Sheets.Select is selecting the 1st worksheet in the workbook. It is better
to call out the name of the worksheet if you need to select some other
worksheet like
sheets("Sheet2").
 
Are you sure you're looking at the correct workbook?

Sheets.select

should select all the sheets of the activeworkbook.
 
I've tried listing the actual pages (no response)
Windows("Loan Recovery 12mo Liq by CO Compiled n.xls").Activate
Sheets(Array("Charts", "Rollup", "ARP", "Auto Finance", "RSB",
"Commercial", _
"Credit Card", "DDA", "Direct", "FUHEB", "HEL", "Lease", "FUMC",
"TMS - PEL", "PEL", _
"Revolving", "TMS", "DFS", "W & T")).Select

and selecting all sheets regardless of name

Windows("Loan Recovery 12mo Liq by CO Compiled n.xls").Activate
Sheets.Select

Neither is working.

To force it to work, I've inserted a break and I'm manualy selecting all
sheets each time it stops (it's in the middle of loop).

The only other thing I can think of that might affect it is that I'm
toggling between two workbooks. The second workbook contains worksheets with
the same title except "Charts".

Could that trip it up even though the second workbook would not be active at
the time?
 
I figured it out thanks to the help from both of you (Joel & Dave). When I
originally developed the macro, I didn't have the worksheet "Charts". I'd
added a line to hide that sheet so that the changes I made to the rest of the
sheets didn't affect that worksheet. I didn't realize that with that sheet
hidden, "Sheets.Select" wouldn't select the 'visible' sheets.

That also explains why I would run across the same problem in other
workbooks as we've added information to many of our workbooks over the past
year.

Thanks to you both!
 
Maybe qualifying the sheets would be enough:

with workbooks("Loan Recovery 12mo Liq by CO Compiled n.xls")
.activate
.sheets.select
end with
 
I get an error if I try to select sheets that are hidden.

If you didn't get an error, then maybe you were masking it with
"on error resume next"

It's usually a pretty dangerous programming practice to leave that switch on for
long portions of code--or to hide errors/conditions that should be programmed
around.
 
Back
Top