copy/paste values for all sheets in workbook
 

guys:

I have the code below to copy and paste all vales for all workbooks in
the active worksheet. for some reason it doesn't do it for all sheets
though. is there anything wrong with the code and/or a better way to
write this?

Thanks,

Joshua

Sub SheetCopyValues()
Application.ScreenUpdating = False

For i = 1 To ActiveWorkbook.Worksheets.Count

Worksheets(i).Activate
On Error Resume Next
Cells.Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

copy/paste values for all sheets in workbook
 

Hi Joshua,

Try:

'=============
Public Sub SheetCopyValues()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook

For Each SH In WB.Worksheets
With SH.UsedRange
.Value = .Value
End With
Next SH
End Sub
'<<=============


---
Regards,
Norman


"cass calculator" wrote in message
ps.com...
guys:

I have the code below to copy and paste all vales for all workbooks in
the active worksheet. for some reason it doesn't do it for all sheets
though. is there anything wrong with the code and/or a better way to
write this?

Thanks,

Joshua

Sub SheetCopyValues()
Application.ScreenUpdating = False

For i = 1 To ActiveWorkbook.Worksheets.Count

Worksheets(i).Activate
On Error Resume Next
Cells.Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

copy/paste values for all sheets in workbook
 

On May 31, 6:08 pm, "Norman Jones"
wrote:
Hi Joshua,

Try:

'=============
Public Sub SheetCopyValues()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook

For Each SH In WB.Worksheets
With SH.UsedRange
.Value = .Value
End With
Next SH
End Sub
'<<=============

---
Regards,
Norman

"cass calculator" wrote in message

ps.com...

guys:

I have the code below to copy and paste all vales for all workbooks in
the active worksheet. for some reason it doesn't do it for all sheets
though. is there anything wrong with the code and/or a better way to
write this?

Thanks,

Joshua

Sub SheetCopyValues()
Application.ScreenUpdating = False

For i = 1 To ActiveWorkbook.Worksheets.Count

Worksheets(i).Activate
On Error Resume Next
Cells.Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Alas, that is much smarter. Thank you !