How to split spreadsheet into separate workbooks using Excel VBA?

Earlier we have split our data into separate excel sheet but this time, we will split spreadsheet records into a new workbook. It’s useful and reduces our effort in making different workbook with same data.

Hope you have enjoyed our earlier blogs, revisit “How to split excel data into separate spreadsheets using VBA“.

Split spreadsheet data into new excel workbooks:

  1. We need to split the records as per state wise
  2. Save as new workbook with the name of the state

We take our earlier data which looks like below:

raw-data-of-State-Records-Excel-VBA

Please look at the column state. If you do it manually then you need to apply a filter and then chose one state name. After that, you need to copy the records and press Ctrl + N to create a new workbook and then paste the records. And then you need to save the file in your desired location. After that, you will go to the second state.

Just think how long time will it take?

Not only that if any new records inserted into your raw sheet you need to do the entire stuff once again. But at the same time, a single piece of code will automate the entire process.

If it’s your first time with Macro then Click here to try our free Excel macro tutorial.

Step by Step guide on how to split excel sheet:

  1. Press Alt + F11 to open VBA editor
  2. Insert a Module from Insert module
  3. Copy the below code and Paste in the code window
  4. Press F5 to execute the below VBA code

Use this Macro Code:

Sub ExtractToNewWorkbook()

Dim ws     As Worksheet

Dim wsNew  As Workbook

Dim rData  As Range

Dim rfl    As Range

Dim state  As String

Dim sfilename As String

Set ws = ThisWorkbook.Sheets("emp")

'Apply advance filter in your sheet

With ws

Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 11).End(xlUp))

.Columns(.Columns.Count).Clear

.Range(.Cells(2, 6), .Cells(.Rows.Count, 6).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

 
For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))

state = rfl.Text

 

Set wsNew = Workbooks.Add

sfilename = state & ".xlsx"

'Set the Location

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sfilename

Application.DisplayAlerts = False

ws.Activate

rData.AutoFilter Field:=6, Criteria1:=state

rData.Copy

Windows(state).Activate

ActiveSheet.Paste

ActiveWorkbook.Close SaveChanges:=True

Next rfl

Application.DisplayAlerts = True

End With

ws.Columns(Columns.Count).ClearContents

rData.AutoFilter

End Sub

In my case, I have saved all workbooks where this file is located. My file is saved in (“C:\Users\Desktop\State Name Separate in a New workbook “ ) this location.
So all the news files will be created and saved in the same location.  Now my folder looks like below

New-file-created-save-same-location-VBA

Only one file is present there. Now if you run the code then all the new files will be created here and the folder looks like below picture:

Run-code-created-new-files-Excel-VBA

It will take hardly 1 min to complete the task. Isn’t that interesting? Moreover, if you add new records in your raw spreadsheet what will have to do is you need to delete all the files before running the macro code.
You can also add a button in your excel sheet and assign the macro to that button. You have already discussed the same in our earlier blog.

Now I will discuss the macro code part:

First of all, I have applied an advanced filter on States in my raw spreadsheet. Then I have stored the entire state’s name in a variable.

Then I have applied a loop. Every time it will get a new state name it will create a new workbook and save the file in the location and then paste the records in the new workbook. After pasting the data in the new worksheet, it will close the new workbook.

The entire process will go on until it will copy and paste till the last state (Split spreadsheet data).  If you wish you can add a msgbox at the end of your macro code to get the information that it has successfully run the macro code.

Note: Please note that before you run the code please check that there is no other file with the same name. For example, if you run the code for the second time it will give you the following error as the same file already exists in your location. It will look for your confirmation.

Error-file-already-exists-Excel-VBA

Now if you press the cancel button it will give you an error as shown below:

Microsoft-visual-Basic-Error

We can overcome the scenario in two ways:

When you are saving the new workbook, you need to check whether the file name exists or not. If it exists then you need to delete it from the folder or the best option is that when you are saving the file you can save the file with the current date.
To do the same, you need to add or change the below red color lines.

Code-Excel-VBA

I will suggest to delete the earlier file and re-run the code.

You can also Learn: What is calculate ratio in excel?

12 Responses
  1. Sasha

    Windows(storeName).Activate on this line getting Error 9 in run time error: Subscript out of range.

  2. Michael

    Hi,

    When we set the range, what does rFL stand for? And how can I change this to reference a table instead of .rows.count, 13 etc?

  3. vinod

    Hi,
    I want to apply the advanced filter for multiple columns so how can i add that feature in this program, Please help me.

  4. KrS

    Hi,
    Great macro, thanks! I would like to ask if it is possible to keep the other worksheets of the document also.
    I have a similar task ahead of me, and this macro is perfect for me, but I have some other worksheets in my workbook which would need to be saved as well (without any change on them)

  5. sangeetha

    HI

    I got Run-Time Error ‘9’
    Subscript out of range

    Windows(state).Activate
    This line is throwing the error, the file isnt saving with the State name, this is the part that isnt working
    sfilename = state & “.xlsx”

Leave a Reply

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.