Is it possible to use VBA in order to select certain worksheets in an Excel workbook and save as new workbook file?
2 Answers
Yes, here is a sample.
Sub Sheet_SaveAs()
Dim wb As Workbook
Sheets("Sheet1").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Path & "\Workbookname.xlsx"
'.Close False
End With
End Sub
Thanks Petr. I managed to hack together the code below by recording myself and Googling the rest as I needed to save several workbooks from the one and automatically add the date. It seems to be working.
Sub Seperate_Sheets()
Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Path1 = ActiveWorkbook.Path & "\" & "Tracker 1" & Format(Now, " dd-mm-yyyy ")
Path2 = ActiveWorkbook.Path & "\" & "Tracker 2" & Format(Now, " dd-mm-yyyy ")
Path3 = ActiveWorkbook.Path & "\" & "Tracker 3" & Format(Now, " dd-mm-yyyy ")
Sheets(Array("sheet1", "sheet2", "sheet3")).Move
ActiveWorkbook.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets(Array("sheet3", "sheet4", "sheet5")).Move
ActiveWorkbook.SaveAs Filename:=Path2, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets(Array("sheet6", "sheet7", "sheet8")).Move
ActiveWorkbook.SaveAs Filename:=Path3, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub
-
Glad it's working. If you'll have to do many of these then looping (e.g., for loop) might be of great help in the future. Oct 23, 2014 at 13:35