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.

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


In article ,
Alan P wrote:

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.

Alan,

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


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


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

Chip and JE, thanks for your help. Works fine now.

"Alan P" wrote:

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.