Macro Doesn't Work on Hidden Sheets

[Alan P]

Yesterday I posted the question below and got some great help. However, as I
went to a different workbook I found that the presence of some hidden
worksheets stopped the macros cold. I've tried a number of alternatives but
can't find the key. Any thoughts are appreciated.

Thanks,


------------------------------------------
I'm trying to paste the values of all cells in all worksheets in a workbook.
Can anyone figure out why this doesn't work for all sheets? The same
structure works for other things, so I'm confused.

Sub Paste_Value_All_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next ws
End Sub


Thanks for any insights.


Was this post helpful to you?
Reply Top





JE McGimpsey 10/31/2005 8:50 AM PST

Answer

Cells defaults to the activesheet. You have to explicitly qualify Cells
for each ws. You also can't select cells on an inactive sheet.

One way:

Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
ws.Select
Cells.Select
'as original
Next ws


Better, since it doesn't require selection:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
..Value = .Value
End With
Next ws


Click to show or hide original message or reply text.

I'm trying to paste the values of all cells in all worksheets in a workbook.
Can anyone figure out why this doesn't work for all sheets? The same
structure works for other things, so I'm confused.

Sub Paste_Value_All_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next ws
End Sub


Thanks for any insights.

Did this post answer the question?
Reply Top





Chip Pearson 10/31/2005 8:59 AM PST



Alan,

Change
Cells.Select
' to
WS.Select
WS.Cells.Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com