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

Copy

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.

Syntax

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
            oTargetWorkBook.Close
            
            '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

Share on Facebook

Tweet