copy/paste values for all sheets in workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cass calculator - 31 May 2007 22:50 GMT

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

Reply to this Message

Norman Jones - 31 May 2007 23:08 GMT

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

> guys:
>
[quoted text clipped - 24 lines]
> Application.ScreenUpdating = True
> End Sub

Reply to this Message

cass calculator - 31 May 2007 23:36 GMT

On May 31, 6:08 pm, "Norman Jones" <normanjo...@whereforartthou.com>
wrote:

> Hi Joshua,
>
[quoted text clipped - 47 lines]
> > Application.ScreenUpdating = True
> > End Sub

Alas, that is much smarter. Thank you !

Reply to this Message