Convert Tabs to Workbook

[KiloHotel]

Hi Guys,

Is there a quick way to transfer tabs in a single workbook to their own workbook, Basically i have one excel file with 200+ Tabs, Each tab now needs to a file to itself so 200+ workbooks.

At the moment i am just copying and pasting and saving, Just looking for the quicker workflow.

Thanks.

 

[PatOBrien198]

Right click the tab, select "Move or Copy", Check the box "Create a copy", in the "To book" drop down pick "(new book)"
Then you can "File", "SaveAs"

or you could run the following code that takes all sheets (tabs) and creates a new workbook with the tab name as the name of the workbook... saved in the same folder as the main workbook.

Sub TabToBook()

Dim ws As Worksheet
Dim strFolder As String

strFolder = ActiveWorkbook.Path & "\"

For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    
    ActiveWorkbook.SaveAs Filename:=strFolder & ws.Name & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    
    ActiveWindow.Close
Next

MsgBox "Done", vbExclamation, "Thanks Pat"

End Sub

 

[KiloHotel]

Perfect! That worked a charm, Thanks Pat!

 

[mbtedrick]

Right click the tab, select "Move or Copy", Check the box "Create a copy", in the "To book" drop down pick "(new book)"
Then you can "File", "SaveAs"

or you could run the following code that takes all sheets (tabs) and creates a new workbook with the tab name as the name of the workbook... saved in the same folder as the main workbook.

Sub TabToBook()

Dim ws As Worksheet
Dim strFolder As String

strFolder = ActiveWorkbook.Path & "\"

For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
   
    ActiveWorkbook.SaveAs Filename:=strFolder & ws.Name & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
   
    ActiveWindow.Close
Next

MsgBox "Done", vbExclamation, "Thanks Pat"

End Sub

Is there a way to save all these documents in a folder within the folder the original document is in?