Press "Enter" to skip to content

Split Excel Data Into Multiple Workbooks In Excel VBA

Kaustubh Patel 0

I have an excel file consisting of customer or user wise data and now I want to share this data with them separately. That means, X data will be shared with X customer and similarly, Y data will be shared with Y customer.

If this problem sounds familiar to you then you’re at the right place. Here, in this article, I will tell you how to split your data into separate worksheets and then save the same in a separate individual workbook.

Basically, this trick will first split your data into separate worksheets and then export each worksheet into a new workbook. This workbook will get saved in a folder where my initial source file is available.

Of course, all these activities will be done automatically. Here, I am not going to share the manual way to do this. I am sure you how to do this manually.

So, What are we waiting for? Let’s know how to export excel data to another workbook?

How To Export And Save Each Worksheet As New Workbook In Excel?

This entire process involved three steps as mentioned below

  1. Create a Pivot Table
  2. Split into Worksheets
  3. Export to separate Workbooks

Out of these three steps, only Step 3 will require VBA (Visual Basic Application) knowledge.

Are you a beginner in VBA? No Problem. For your convenience, I will also attach a premade VBA code to export the worksheet to the new workbook VBA.

But before starting with the article. Let’s have a basic idea of the data available with me. You can download this file from below.

I have a customer wise data for the amount credited to their bank account in the month of April 2020. Now, I want to share this data with all the customers but provided they only get data related to their code. For example, ABC limited should receive only related to his code (DE1001). Similarly, the other customers should also receive the data related to their account only.

One of the ways to do this task is – First, filter the data at the customer level and then copy the filtered and visible data. Second, create a new workbook and paste the data into it. Third, save the newly created workbook in a separate folder.

I need to repeat this process until I cover all the customers mentioned in the excel file.

Of course, this is a time-consuming, monotonous and unproductive process and may take 1 to 2 hours to complete this.

Hence, there is no logic in doing this task manually.

But, What is another way to do this? How to split data into multiple files in excel?

To get this answer, please read the below article completely. In the below article, I have discussed all the steps in detail with a proper explanation.

#1. Create A Pivot Table In Excel

This is the first step to export data into worksheets and then save all the worksheets as a separate file with a worksheet name as a file name.

Create Pivot Table In Microsoft Excel
Create Pivot Table In Microsoft Excel

Note: The customer code added to the top under the ‘Report Filter’ field is very important here. Rest, you can change it as per your requirement.

#2. Export Your Data Into Separate Worksheets

This is the next step involved in exporting excel data into the separate workbook.

All you need to do is just click on the ‘Filter Feild’ and go to ‘Option’ from the pivot menu ( pivot menu will start appearing on the top bar once you click on the pivot table).

Now, open the drop-down menu of ‘Options’ available on the left side of the menu bar and select ‘Show Report Filter Pages’ from available options.

Next, select the appropriate option and click ‘Ok’ to split the data into worksheets automatically.

Now, your data will look something like this.

Split data into Worksheets in MS Excel
Split data into Worksheets in MS Excel

#3. Split Worksheets Into Separate Workbooks

This is the last step to export your excel data into the separate workbook. But, this step can’t be done with normal excel functions or formula. Instead, here, you will require a VBA code to do it.

As told earlier. for your convenience, below, I have shared the premade VBA code to save each worksheet as a new file.

All you need to do is open the VBA editor or press Alt+F11, insert a new module and paste the above code.

The first code will Copy and Paste the pivot data into text format.

Sub Copy&Paste ()
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Visible = True Then
            Sh.Activate
            Sh.Cells.Copy
            Sh.Range("A1").PasteSpecial Paste:=xlValues
            Sh.Range("A1").Select
            Sh.Cells.Select
            Selection.Columns.AutoFit
            
        End If
    Next Sh
    Application.CutCopyMode = False
End Sub

Whereas the second code will export all the worksheets to a separate workbook file.


Sub SheetsToWorkbooks()
'Step 1:  Declare all the variables.
    Dim ws As Worksheet
'Step 2: Turn screen updating off to speed up your macro code
    Application.ScreenUpdating = False
'Step 3:  Start the looping through sheets
    For Each ws In ThisWorkbook.Worksheets
'Step 4:  Copy the target sheet to the new workbook
    ws.Copy
'Step 5:  Save the new workbook with sheet name.
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name
    ActiveWorkbook.Close SaveChanges:=True
'Step 6:  Loop back around to the next worksheet
    Next ws
'Step 7: Turn screen updating on
    Application.ScreenUpdating = True
End Sub

Next, run the macro and sit back to see the magic.

The above code will automatically copy and paste the pivot data and create a separate workbook for all the worksheets available within the file. The filename of the workbook will be copied from the worksheet name.

It means your worksheet name will be the name of the newly created workbook.

Note: The above code will save all the workbooks at the location where the original file (which includes macro in it) is saved. Hence, it is very important that the original file is saved separately in a new folder.

At last, it will create a new workbook for all the worksheets. For more information, please refer to the below-attached snapshot.

Export Worksheets into new workbook
Export Worksheets into new workbook

Over to You

Now, within a minute you can export any data into separate files and then email these files to your respective users or customers.

There is no need to sit back and do this activity manually.

At last, if you face any problem in exporting your data or else you have any query or question then feel free to contact us or share your message in below comment box.

Leave a Reply

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