Copy All values VBA script failing

Question

I am trying to create a VBA script that copies all data in a whole workbook as pastes as values, then saves as a new workbook, thus removing all formulas.

Here is my code:

Sub MakeAllVals()
Dim wSheet As Worksheet

For Each wSheet In Worksheets
    With wSheet
        .UsedRange.Copy
        .PasteSpecial xlValues
    End With
Next wSheet

Application.Dialogs(xlDialogSaveAs).Show
End Sub

I'm getting a runtime error 1004 on the .PasteSpecial xlValues command but I can't work out why.

How can I accomplish the goal of pasting all data as values and saving as a new workbook?

possible duplicate of: http://stackoverflow.com/questions/20671795/how-to-remove-formulas-from-sheet-but-keep-their-calculated-values — Goos van den Bekerom, Oct 27 '14 at 12:20

score 2 · Answer 1 · answered Oct 27 '14 at 12:48

You were close just have to move UsedRange up next to wSheet

Sub MakeAllVals()
Dim wSheet As Worksheet

For Each wSheet In ActiveWorkbook.Worksheets
    With wSheet.UsedRange
        .Copy
        .PasteSpecial xlValues
    End With
Next wSheet

Application.Dialogs(xlDialogSaveAs).Show
End Sub

score 2 · Accepted Answer · answered Oct 27 '14 at 18:59

You just need to paste to a range in the new sheet. Currently you are not pasting within the new book and you are not pasting within a range.

Sub MakeAllVals()
    Dim oldBook As Workbook, oldSheet As Worksheet
    Dim newBook As Workbook, newSheet As Worksheet

    Set oldBook = ThisWorkbook      ' Set to the formula workbook you want to copy from
    Set newBook = Workbooks.Add     ' Make the new workbook you want only values in

    For Each oldSheet In oldBook.Sheets   ' Loop through all of the sheets in the formula book
        Set newSheet = newBook.Sheets.Add ' Make a new sheet in the newbook to add the values to

        newSheet.Name = oldSheet.Name & " Values" 
        oldSheet.UsedRange.Copy                   
        newSheet.Range("A1").PasteSpecial xlValues ' Paste in a range on the new sheet as Values
    Next oldSheet

    Application.Dialogs(xlDialogSaveAs).Show ' Show Save As Dialog window
End Sub

Copy All values VBA script failing

2 Answers2