Copy, Paste Value, Save Worksheet - Multiple Worksheets

Question

I have a workbook with many worksheets. I am attempting to use the below macro to cycle the worksheets, copy and paste value, then save off individually in a location.

I feel like I'm glossing over something very small and beginning to go bonkers. Currently this code copies and pastes value the first worksheet, and then saves the rest off without the copy/paste. So everything is working as desired with the exception of the copy/paste value not occurring with each worksheet.

Sub SaveFilesInFolder()
'
'This is for saving each worksheet as a workbook in a destination folder as an excel file
'

'
    Dim sh As Worksheet
    Dim wb As Workbook
    
    For Each sh In Worksheets
    
        With ActiveWorkbook
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
               
        SheetName = sh.Name
        sh.Copy
    
            .SaveAs Filename:="C:\Location\" & SheetName
            .Close SaveChanges:=True
        End With
    
    Next sh
 
End Sub

Any and all assistance is greatly appreciated.

Edit:

Below is the updated code from comments. Unfortunately, the sheet is still copying/pasting for the first worksheet and not the rest. Everything is saving in the specified location as intended.

Sub SaveFilesInFolder()
'
'This is for saving each worksheet as a workbook in a destination folder as an excel file
'

'
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim rng As Range
    
    For Each sh In ThisWorkbook.Worksheets
    
        Set rng = Cells
    
        rng.Copy
        rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
               
        sh.Copy
        ActiveWorkbook.SaveAs ("C:\Location\" & sh.Name)
        ActiveWorkbook.Close
    
    Next sh
 
End Sub

You do not need to rely on the `ActiveWorkbook`. Explicitly define the book/sheet/range you are working with. — urdearboy, Aug 11 '22 at 21:42
And you probably want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Aug 11 '22 at 21:52
See this SO question; https://stackoverflow.com/questions/36797575/save-each-worksheet-as-new-workbook — GMalc, Aug 11 '22 at 22:03

score 0 · Answer 1 · answered Aug 12 '22 at 02:53

0

Try it without the clipboard. I've also turned off alerts (for saving over files) and done a small amount of clean up.

Sub SaveFilesInFolder()
'
'This is for saving each worksheet as a workbook in a destination folder as an excel file
'

'
    On Error GoTo e

    Application.DisplayAlerts = False
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
    
        With sh.UsedRange
            .Value2 = .Value2
        End With
        
        sh.Copy
        ActiveWorkbook.Close True, "C:\Location\" & sh.Name
    Next sh
e:
'   Ensure alerts are turned back on before re-throwing.
    Application.DisplayAlerts = True
    If Err > 0 Then Err.Raise Err
End Sub

answered Aug 12 '22 at 02:53

SSlinky

427
2
9

Thank you for your comment. I am running into the same snag as the above commented code. I'm attempting to copy/paste value Pivot Tables. Searching on the error "Run-time error '1004': Cannot enter a null value as an item or field name in a PivotTable report" isn't bringing up many useful articles or posts. Looks like maybe there is a Null value somewhere in the PivotTable that is causing the issue. Unfortunately, I am not finding any NULL values within the Pivot Table. – Sean Pakulski Aug 12 '22 at 17:31
@SeanPakulski this is a new error and maybe a new question? – SSlinky Aug 13 '22 at 16:04

VBasic2008 · Answer 2 · 2022-08-12T09:06:32.290

Export Worksheets

To leave the source workbook intact, convert formulas to values in the destination workbooks.

Sub ExportWorksheets()

    Const dFolderPath As String = "C:\Location\"

    Dim swb As Workbook: Set swb = ThisWorkbook

    Dim dPath As String: dPath = dFolderPath
    If Right(dPath, 1) <> Application.PathSeparator Then
        dPath = dPath & Application.PathSeparator
    End If

    Application.ScreenUpdating = False
    
    Dim sws As Worksheet
    Dim dwb As Workbook
    Dim dws As Worksheet
    Dim drg As Range

    For Each sws In swb.Worksheets

        sws.Copy ' copied to a new single-worksheet workbook

        Set dwb = Workbooks(Workbooks.Count) ' the last
        Set dws = dwb.Worksheets(1) ' the one and only
        Set drg = dws.UsedRange

        drg.Value = drg.Value ' formulas to values

        Application.DisplayAlerts = False ' to overwrite without confirmation
            dwb.SaveAs dPath & dws.Name
        Application.DisplayAlerts = True

        dwb.Close SaveChanges:=False ' it's already been saved

    Next sws

    Application.ScreenUpdating = True

    MsgBox "Worksheets exported to single-worksheet workbooks.", vbInformation

End Sub

Thank you for your comment. With the above code, it works with formulas,; however, in my case I am attempting to copy/paste values Pivot Tables. — Sean Pakulski, Aug 12 '22 at 17:22

Copy, Paste Value, Save Worksheet - Multiple Worksheets

2 Answers2

Export Worksheets