Saving All Tabs as Individual Files (Macro)

[dominicwellsuk]

Hello there,

I'm looking to create a macro that takes every tab in a workbook and saves it as an individual file. An individual would be working on a single workbook with multiple tabs, and would periodically make individual file copies of each tab into a folder for a supervisor to review, without having to go into that persons file.

Got this for copying the tab to a new workbook, and saving that:

ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.Name = "Concern Document"
ActiveWorkbook.SaveAs Filename:="NEW FILE NAME WHATEVER THAT IS", _ CreateBackup:=False
ActiveWorkbook.Close

But how do I cycle through the tabs to pick them out for saving?

Thanks

 

[VoG]

Try like this

Sub CopySheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    With ActiveWorkbook
        .SaveAs Filename = ws.Name & ".xls"
        .Close
    End With
Next ws
End Sub

 

[dominicwellsuk]

Thanks, as a test my code is now:

Sub CopySheets()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Copy
With ActiveWorkbook
.SaveAs Filename = "D:\" & ws.Name & ".xls"
.Close
End With
Next ws

End Sub

When I run this I get an error as the macro seems to be trying to save the file as FALSE.xls, so the worksheet name is not registering. This happens even if I set a fixed file name to save it as, i.e. .SaveAs Filename = "D:\test.xls"

Any ideas?

 

[VoG]

Sorry, I missed out a :

Sub CopySheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    With ActiveWorkbook
        .SaveAs Filename:="D:\" & ws.Name & ".xls"
        .Close
    End With
Next ws
End Sub