Printing/saving excel workbook with multiple tabs as singular excel files based on cell content

[thmartin]

Hello,

I have an excel file containing multiple tabs that i need to print/save as individual excel files. The name of each excel file is a given cell on the individual worksheet.

Example:

Sheet1 cell C6 = "Target Revenue" - I would need to print a new excel file saved in the same folder named "Target Revenue" containing the contents of just Sheet1
Sheet2 cell C6 = "Target GP" - I would need to print a new excel file saved in the same folder named "Target GP" containing the contents of just Sheet2

There are numerous tabs, and i have been doing this manually with the "Move or Copy" feature, but i feel as though there must be an easier way.

Thanks,

 

[John_w]

Try this macro.

Public Sub Save_Each_Sheet_As_Workbook()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False  'suppress warning if .xlsx file already exists - file is replaced
    
    With ActiveWorkbook
        For Each ws In .Worksheets
            ws.Copy
            ActiveWorkbook.SaveAs .Path & "\" & Range("C6").Value & ".xlsx"
            ActiveWorkbook.Close SaveChanges:=False
        Next
    End With
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

 

[thmartin]

This worked perfectly! Thank you!