Copy each sheet in a new Excel file and Save with Sheet name using VBA

Copy each sheet in a new Excel file and Save with Sheet name using VBA


Method exposed by Sheet/Worksheet object which copy the entire sheet with its structure and formatting. In this article we will copy each sheet and insert into a new workbook and save workbook with sheet name to a common location.


expression.Copy Before, After

Code example

Public Sub SaveEachTabIntoFile(fileLocation As String)
    'Check validate file path
    If fileLocation <> "" Then
        'Declare worksheet object
        Dim oWorkSheet As Worksheet
        'Declare workbook object
        Dim oWorkBook As Workbook
        'Bind active workbook reference
        Set oWorkBook = ActiveWorkbook
        'Iterate each sheet from current workbook
        For Each oWorkSheet In oWorkBook.Worksheets
            'Declare new workbook object
            Dim oTargetWorkBook As Workbook
            'add workbook and bind reference
            Set oTargetWorkBook = Workbooks.Add
            'Copy sheet
            oWorkSheet.Copy Before:=oTargetWorkBook.Worksheets(1)
            'Save file wth sheet name
            oTargetWorkBook.SaveAs fileLocation & "\" & oWorkSheet.Name & ".xlsx"
            'Close workbook
            'Memory cleanup
            Set oTargetWorkBook = Nothing
        Next oWorkSheet
        'Memory cleanup
        Set oWorkSheet = Nothing
        Set oWorkBook = Nothing
    End If
End Sub

Calling method

Sub SaveAllSheets()
    'Call method
    Call SaveEachTabIntoFile("C:\Users\Downloads")
End Sub

Next>> Worksheets vs Sheets in Excel Add-in

Leave a Reply

Your email address will not be published. Required fields are marked *