Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner
Why Stack Overflow recommends it
Stack Overflow users cite it as the source of a copy-paste VBA split-by-column macro, usually when asking why that code throws AutoFilter errors or how to adapt it to their own data.
Recommended by 1 people across 1 different months, 2018 to 2022.
What it is
A 13-minute beginner tutorial from Caripros HR Analytics showing how to write an Excel VBA macro that splits a master data sheet into separate sheets by a chosen column such as Department or Country. It walks through copying the sheet, applying AutoFilter, deleting non-matching rows, and looping over a list of split codes.
What people said
Recommends TsiT · 2020-01 · Stack Overflow · on “Split a Master Spreadsheet into Multiple Sheets in Excel with VBA”
Here is the code I got from this helpful YouTube video: [this video] Sub SplitandFilterSheet() 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:=4, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ActiveSheet.AutoFilter.ShowAllData Next cell End Sub PROBLEM: This almost works for me. It splits the Master Data into multiple tabs. The tabs are named correctly with unique employee ID's. But the c
Cites ZelelB · 2018-07 · Stack Overflow · on “How to split spreadsheet into multiple files, and in each new file split data into multiple sheets?”
I would like to split my data with a VBA macro into multiple files, based on some criteria, and then in the new created files, split again according to another column filter (criteria). Basically, there is two Youtube-Tutorials explaining each step alone, but I don't get it to mix them. First one to split the main spreadsheet into files, and works for me: [LINK] Second one to split a spreadsheet into multiple sheets: [this video] The second doesn't actually have exactly my needs, as you have to do some manual steps, like selecting and naming the range that you need (i.e. "Master" and "SplitCode"). I have so many data and files to split, so I would like to have a macro splitting it automatically,
Cites NantyNarking · 2021-03 · Stack Overflow · on “Split a Master Workbook into Multiple Workbooks in Excel using VBA”
I got this code from a YouTube tutorial ( [this video] ). 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:=4, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ActiveSheet.AutoFilter.ShowAllData Next cell End Sub I receive an error Tha
Cites 286642 · 2022-08 · Stack Overflow · on “Autofilter method of Range Class Failed trying to create sub sheets based on master data”
I understand this error has popped up many times before on this site, but I am using VBA for the first time. I did what research from other posts I could, but, I just don't have the experience to gain much from them. I was following the steps in this [this video] YouTube video so that I could create separate sheets based on my master data. I specifically need one sheet per different Contact, found in my 6th column. There are more than 900 rows in the master, but there are 61 unique contacts, so I would expect 61 sub sheets to be generated. [LINK] Note that I titled the above data, "Master," and my Sheet2, which lists all unique text entries (there were some accidentally-entered numerical entries