Create separate workbook from worksheets VBA

Split worksheets into separate files

Macro to create multiple workbooks from a master worksheet

Recently as part of a consulting assignment I had a task to create separate workbook from all the existing worksheets in the file. I wrote the below code for the purpose. May be if you have a similar requirement you can use this code with little or no amendment.

Sub makefiles()

Dim wb As Workbook, wbnew As Workbook

'creating variables to store workbook

Dim st As Worksheet

'creating variable to store a worksheet

Set wb = ThisWorkbook

Application.DisplayAlerts = False

'when a sheet is deleted, excel throws an alert; the above code is to skip such alerts; if not it will throw a dialog box

For Each st In ActiveWorkbook.Sheets

'Loop to repeat a set of commands

If st.Name <> "Summary" Then

'My requirement was such that I didnt want a workbook created for sheet Summary thus the above code

Workbooks.Add

'adds a new workbook

ActiveSheet.Name = "Rough"

'new workbook has only 1 sheet; renaming it so that it can be deleted easily; optional you can use sheet1 instead

Set wbnew = ActiveWorkbook

wb.Activate

st.Select

st.Copy before:=wbnew.Sheets(1)

'this code copies the sheet and moves it to the new workbook

wbnew.Activate

Sheets("Rough").Delete

Range("a1").Select

wbnew.SaveAs wb.Path & "\" & st.Name & ".xlsx"

'save the new workbook in the same folder as the master file

wbnew.Close

'As always drop an email to info@upskilltechnologies.com for any excel or VBA related work

'Or visit us at https://www.upskilltechnologies.com/learn-vba to watch VBA videos

wb.Activate

End If

Next st

End Sub