Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to create workbooks from worksheets using VBA. Let’s roll
In the screenshot below, we have transaction records for each of our Payment Type. In total, we have Skrill, MasterCard, VisaCard, Wester Union and PayPal. Our goal is to write VBA code that will create workbooks from each of our Payment Method worksheet!
In the screenshot below, we have a folder that houses the workbook that contains all the worksheet we want to make standalone workbooks from.
By the type we finished writing the code and run, we must have the newly created workbooks individually
To access the Visual Basic for Applications code window, we need to have the developer tab enabled. You can reach how to enable developer tab here. Or, we can use the keyboard shortcut ALT + F11 to open the VBA editor.
In the VBA edition, click on Insert tab
In the code window, execute the following codes:
Dim myData As String
myData = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each mysheet In ThisWorkbook.Sheets
Application.ActiveWorkbook.SaveAs Filename:=myData & “\” & mysheet.Name & “.xlsx”
Click on the RUN button or deploy F5 on the keyboard to run the codes
From the caption below, we can see that we have the workbooks created and housed in the folder.
This is awesome because it saves time