Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner

New course Launched! I created it to show you step-by-step how to design a salary structure with regression analysis in Excel. Check out the detail here:

Topic: Split a master tab into multiple sub tabs with 1 click
Scenario: You want to split the data on a master file into multiple small sub-tabs by a chosen criteria (eg. Department, Country, etc.)
Function: Macro for Copy sheet, AutoFilter, and Loop

Workbook download:

Related Video:

***Macro Code SEE COMMENT FOR IMPORTANT NOTICE***
Sub SplitandFilterSheet()
'Step 1 - Name your ranges and Copy sheet
'Step 2 - Filter by Department and delete rows not applicable
'Step 3 - Loop until the end of the list
Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("Splitcode")

For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=6, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.AutoFilter.ShowAllData
Next cell
End Sub

******Follow-up Consulting Services******

******More Videos in Playlists******

#ExcelforHR#HRAnalytics#Excel#HR

[selenevera]

Thanks, this code works for me:
Sub Macro1()

Dim splitcode As Range
Sheets("Master).Select
Set splitcode = Range("splitcode")

For Each cell In splitcode
Sheets("Master").Copy
ActiveSheet.Name = cell.Value

With
.AutoFilter Field:=1, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
.Range("A2").Select
Range(Selection,
Selection.EntireRow.Delete
End With

Selection.AutoFilter
Next cell
End Sub

[spokenbysergio]

For anyone that is not getting the VBA to work properly, I'm not sure if there was an amendment to the code but here:

With
.AutoFilter Field:=6, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues
.Offset(1,
End With

change "NOT EQUAL TO" to "<>" and that fixed my problem.

[santinoYT]

Worked great for me and saved a lot of time. Tip: Max length for SplitCode range is 31. This is a limitation in the Excel Sheet name with a max length of 31 chars. If you change the SplitCode range's value, the data in MasterData also need to change as they will not match.

[DKAFE]

It took me a bit to figure out the syntax (1 vs L) as I'm unfamiliar with VB. But it finally worked splitting 23890 rows into 13 tabs. thank you so much

[ABEW]

Should note for future users: this runs into issues if you have hidden columns. Unhide them, and then remake your data range, even if it looks like it includes them.

Anyways, this was extremely helpful, and saved me a ton of time in separating cross-sectional data into panel format for regression and analysis in R. Thank you so much.

[DiffyChannel]

Thanks so much!!!
It really saves time and hope to have another video with update/refresh data automatically after splitting multiples sheets.

[nitsiet]

This worked like butter . Only change I did is I replaced "NOT EQUAL TO" with "<>" .

[hisjadedwolf]

Thank you very much, this is exactly what I need for a daily report. Your walk through was super easy to follow and I appreciate you breaking down each part of the code. I'll be setting this up tomorrow for sure.

[dinaoom]

Thank you so much this worked perfectly!!! I had 14000 rows to sort through and arrange by column value.

[Kisembobusiness]

Thank You Very Much. This Has saved me a lot of time. God bless you.

[ambercook]

Just wanted to share that I initially had issues with getting all data to filter for all split tabs, it was only filtering on the first tab and copying all data to the remaining tabs.
My Code:
Sub SplitandFilterSheet()
'Step 1 - Name your ranges and Copy sheet
'Step 2 - Filter by Item Code and delete rows not applicable
'Step 3 - Loop until the end of the list
Dim Splitcode As Range
Sheets("MasterData").Select
Set Splitcode = Range("SplitCode")

For Each cell In Splitcode
Sheets("MasterData").Copy
ActiveSheet.Name = (cell.Value)

With
.AutoFilter Field:=2, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
.Offset(1,
End With


Next cell
End Sub

My Fix: Can cell format from General to Text (my data is text not number) in the SplitCode list and MasterData column 2.

Thank you for an excellent tutorial and sharing your code!

[capitalbee]

This Script is awesome took me a week or so to truly get it working right...but once I did IT'S AWESOME

[svetievboris]

This is the most useful VBA script I have found so far. It is exactly what I was looking for. I used macro recorder to add a few tweaks to it. I am just hoping to figure out how to make range reference be made with an inputbox and then used in the script. Thank you very much.

[AlexandriaPonsonby]

Hi, I really appreciate your video's! I work with NDIS and need to split clients and houses into their own spreadsheets (for invoicing purposes). I have an idea of how to split the houses from your video, but not how to split and send the clients to their newly created houses. Please note, the houses & client codes/names that are generated from the NDIS spreadsheets are completely different to the one's on the other spreadsheets and this is also meant to be sent to a pre-made excel template at the same time. Thank for your time and help :-)

[HeenDip]

Pretty much exactly what I'm looking for to break apart a large data set. Well done!

[hogiraldo]

Thank you very much!. Your explanation was throughout. I did not know anything about macros, and I was able to follow. <3

[tlee]

Thank you very much for this step by step tutorial. It finally works for me !

[santoshgalphade]

First Macro & Run Successfully, thanks alot Love you 😊😊

[krimaskitchen]

Thank you very much for this step by step tutorial, I have one question, what if I have filter based on Date ? I'm trying to use your script but "ActiveSheet.Name = cell.Value" gives me Run time error 1004 even I change date format to DD-MM-YYYY. Anyone have any idea on that ?

[sebastianromero]

Thank you so much. I have spent 3 days looking for this solution. This is amazing.

I would like to know if i keep filling rows in my master tab, will the subtabs update automatically?

again, thx alot!