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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On May 31, 6:08 pm, "Norman Jones" <normanjo...@whereforartthou.com>
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" <joshua.moa...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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 !