Copy and PasteSpecial Values into a new workbook

[gsx600f]

I currently have an Excel macro that copies a whole sheet from a workbook into a new workbook, but pastes the values only, and then formats them to fit, ready for e-mailing:

Worksheets("Sheet1&quot.Copy
z = ActiveSheet.Name
ActiveSheet.Range("A1:K250&quot.Copy
ActiveSheet.Range("A1:K250&quot.PasteSpecial Paste:=xlValues
ActiveWindow.Zoom = 100
Range("A1:K250&quot.Select
Selection.Columns.AutoFit
Range("A1&quot.Select

I now have more sheets, in exactly the same format but with different values, in the original workbook and want to copy them as well.

Is it possible to amend this to copy more than one sheet to the new workbook, but keeping them as separate sheets, and only copying the values.


Cheers
Gsx600f

 

[acron]

It's not clear if you want to copy the shjeets to one single workbook, or if you want a new workbook for each sheet.

If you just want to copy to a single workbook, just save the existing file and run

    For i = 1 To Sheets.Count
        Sheets(i).Cells.Copy
        Sheets(i).Cells.PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
    Next

and ave the file with a new name. If you need to remove sheets, that should easy enough to do. The above code justs goes through each sheet, copies and pastes the values.

If you want a neew workbook for each sheet try,

    For i = 1 To ThisWorkbook.Sheets.Count
        ThisWorkbook.Sheets(i).Copy
        Cells.Copy
        Cells.PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
    Next

A.C.

 

[gsx600f]

Thanks for this, I'm just copying all the sheets to one single workbook.

When i try to run this though, I get a Run-time error '438' message with the line:

Sheets(i).Cells.Copy

highlighted on the debug

G.