Tags: active, below, code, copy, excel, guysi, inthe, microsoft, msdn, paste, sheets, software, vales, values, workbook, workbooks, worksheet

copy/paste values for all sheets in workbook

On Microsoft » Microsoft Excel

1,438 words with 1 Comments; publish: Sun, 01 Jun 2008 13:03:00 GMT; (30662.50, « »)

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

All Comments

Leave a comment...

  • 1 Comments
    • On May 31, 6:08 pm, "Norman Jones" <normanjo....excel.todaysummary.com.whereforartthou.com>

      wrote:[vbcol=seagreen]

      > 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....excel.todaysummary.com.gmail.com> wrote in message

      > news:1180648247.097015.39690.excel.todaysummary.com.w5g2000hsg.googlegrou ps.com...

      >

      >

      >

      >

      Alas, that is much smarter. Thank you !

      #1; Sun, 01 Jun 2008 13:05:00 GMT