error - select method failed - (columns.select)

[PBcorn]

Getting a 1004 error - select method of range class failed - with the
following code:

Sub unhide()

Dim sh As Object


For Each sh In ActiveWorkbook.Worksheets

sh.Visible = True

sh.Columns.Select

Selection.EntireColumn.Hidden = False

Next sh

End Sub

the code is in a module. The macro works for the first sheet of the
three-sheet workbook then bombs on the second sheet

Thanks in advance

PB

 

[Chip Pearson]

When you use a For Each loop with the Worksheets collection, the individual
sheets are *not* automatically made active as the loop spins. Also, you
cannot Select a range that is not on the active sheet. Together, these facts
cause the line

sh.Columns.Select

to fail. The sh variable refers to a worksheet that is not active and then
Select attempts to select a range on the sh worksheet. Use

sh.Select
sh.Columns.Select

Of course, it isn't necessary to Select anything, so you can use just

sh.Columns.EntireColumn.Hidden = False

within the loop.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Norman Jones]

Hi P,

Your error is encountered because you
cannot make selections on an inactive
sheet.

Try the following version:

'===========>>
Sub unhide()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
sh.Columns.Hidden = False
Next sh
End Sub
'<<===========

 

[PBcorn]

thanks. as you can tell I am in the early stages of learning VB.