VBA/Macro to select specific worksheets and save them as separate files

[krodriguez]

Hello - I have file that is saved as binary .xlsb and have several tabs. I need to be able to select just a few of the worksheets(tabs) and save them as separate versions. Anyone out there with a code that can assist? Thanks!

 

[JoeMo]

This will get you started.

Sub SaveSheetsAsFiles()
Dim SheetsToSave
SheetsToSave = Array("Sheet1", "Sheet3", "Sheet5") 'change tab names to suit
Application.ScreenUpdating = False
For Each sht In Sheets(SheetsToSave)
    sht.Copy
    'file location is same as workbook the code is in. Change to suit
    'file format is .xlsm - change to suit
    ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsm", FileFormat:=52
Next sht
Application.ScreenUpdating = True
End Sub

 

[krodriguez]

Thanks Joe this works. But how can I save them in one excel instead of 3 different ones? and also If I want to add more subsets like ("Sheet1", "Sheet3", "Sheet5") in 1 file, then "Sheet6", "Sheet7", "Sheet8") in a second file...how can this be done?

 

[JoeMo]

Your OP title and text said you want to save the sheets as "separate files" and "separate versions", respectively. A bit more care in describing what's wanted would save time all around.

To save the sheets to a new (single) workbook, just follow the posted code for the first sheet, then copy subsequent sheets to the workbook created with the first sheet.

 

[krodriguez]

Thanks. Getting an error looks like my function is incorrect to run both macros at once. Can you assist? sorry as you can see trying to learn vba


Sub Run_All_Macros()


Call SaveSheetsAsFiles1
Dim SheetsToSave
SheetsToSave = Array("Rainbow", "RLN-Net Realization RLN-Red Rev RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo") 'change tab names to suit
Application.ScreenUpdating = False
For Each sht In Sheets(SheetsToSave)
sht.Copy
'file location is same as workbook the code is in. Change to suit
'file format is .xlsm - change to suit
ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsm", FileFormat:=52
Next sht
Application.ScreenUpdating = True
End Sub


Call SaveSheetsAsFiles2
Dim SheetsToSave
SheetsToSave = Array("Neocell", "NC-Net Realization", "NC-Red Rev", "NC-COGS NC-Logistics", "NC-R&D", "NC-Selling", "NC-Administrative", "NC-Advertising", "NC-Sales Promo") 'change tab names to suit
Application.ScreenUpdating = False
For Each sht In Sheets(SheetsToSave)
sht.Copy
'file location is same as workbook the code is in. Change to suit
'file format is .xlsm - change to suit
ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsm", FileFormat:=52
Next sht
Application.ScreenUpdating = True
End Sub


End Sub