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

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)




"PBcorn" wrote in message
...
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

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
'<<===========


---
Regards.
Norman
"PBcorn" wrote in message
...
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

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

"Chip Pearson" wrote:

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)




"PBcorn" wrote in message
...
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