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.
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
'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
'this code copies the sheet and moves it to the new workbook
wbnew.SaveAs wb.Path & "\" & st.Name & ".xlsx"
'save the new workbook in the same folder as the master file
'As always drop an email to firstname.lastname@example.org for any excel or VBA related work
'Or visit us at https://www.upskilltechnologies.com/learn-vba to watch VBA videos