Create Workbooks from Worksheets using VBA

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


Sample Data

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

VBA Code

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

Select Module

In the code window, execute the following codes:

Sub myDataInWorksheetToWorkbook()

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”
Application.ActiveWorkbook.Close False


End Sub

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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *