Split Excel Data into Multiple Sheets Automatically

[@KenjiExplains]

🚀 FREE 50 Excel Hacks Template from Hubspot: clickhubspot.com/wcoo

[@navaki333]

This is the one that I was searching. Thanks for tge video for splitting data by using 3 methods. 1. Pivot filter, 2. Vstack + Filter formula and 3. VBA.

[@ganeshkannan5750]

God bless you brother ..! It was so helpful..!

[@khinpainghtwe3315]

Thank you deeply. It makes quite improve to my acquire skill.

[@onadipemayokun]

Thank you so much Kenji.. I'm getting a syntax error on the code however, step 1 and 2 worked just fine😊

[@utsw-gme-medhub5827]

Me, too. Today is 10/31/24. Any info on how to fix it? It's the Sub SplitDataBySelectedColumn () where where the error is found.

[@mohamedmedhat5269]

I used Chatgpt to correct the VBA Formula and it works now here is the new one: Sub SplitDataBySelectedColumn() Dim ws As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim lastRow As Long Dim lastCol As Long Dim uniqueValues As Collection Dim cell As Range Dim value As Variant Dim colToFilter As Long Dim columnHeader As String Dim headerFound As Boolean Dim i As Long ' Use the active worksheet Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) ' Prompt the user to select the column header columnHeader = InputBox("Enter the column header to split the data by (case-insensitive):") ' Find the column based on header value (case-insensitive) headerFound = False For colToFilter = 1 To lastCol If LCase(ws.Cells(1, colToFilter).Value) = LCase(columnHeader) Then headerFound = True Exit For End If Next colToFilter If Not headerFound Then MsgBox "Column header not found. Please try again.", vbExclamation Exit Sub End If ' Create a collection of unique values in the selected column Set uniqueValues = New Collection On Error Resume Next For Each cell In ws.Range(ws.Cells(2, colToFilter), ws.Cells(lastRow, colToFilter)) uniqueValues.Add cell.Value, CStr(cell.Value) Next cell On Error GoTo 0 ' Loop through unique values and create a new worksheet for each For Each value In uniqueValues ' Add a new worksheet and name it after the unique value Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) On Error Resume Next wsNew.Name = CStr(value) On Error GoTo 0 ' Copy the headers ws.Rows(1).Copy wsNew.Rows(1) ' Copy matching rows directly without filtering i = 2 ' Start pasting from row 2 in the new sheet For Each cell In ws.Range(ws.Cells(2, colToFilter), ws.Cells(lastRow, colToFilter)) If cell.Value = value Then cell.EntireRow.Copy wsNew.Rows(i) i = i + 1 End If Next cell Next value End Sub

[@basheeraloshari8400]

thank you so much i was thinking to try the same

[@extraktAI]

This is a great tip, thanks!

[@KenjiExplains]

Glad it was helpful!

[@anjelas2708]

Which version of Excel are you using? That message box while entering Q5 on the validated cell is quite modern looking. I am still getting an old school message box. I am on Excel 365 for enterprise.

[@markpodesta4605]

Thank you Kenji!🙂

[@KenjiExplains]

Thanks for watching :)

[@schubertpeter79]

awesome Kenji!

[@KenjiExplains]

Thank you!

[@camlex6310]

Great video!! Thanks

[@KenjiExplains]

Glad you liked it!

[@AmazingDiscountDeals]

It is great. Thank you

[@KenjiExplains]

Thanks for watching!

[@amitsalunkhe3489]

Hi Kenji, How we can split data in workbooks instead of sheets and criteria will be multiple column header like sub-filter apart from main filter

[@carlosveterano]

The code VBA do not work. Error in "Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))".

[@paulinesalas6064]

Do you have a video that rather than splitting it to worksheet, split it into workbook?

[@govardhanreddy8233]

I was looking for something similar solution for google sheets. Currently I’m using query with filter but working on appscript to automate it

[@bestoptimate]

Hi, My problem is different from the topic of this video, but I have to connect with you for the solution of a small problem that I am facing. When I define a named range and deselect and select the range-address, the marching-ants around the defined range are not displayed. Also, when I select an already defined named-range from the list, the cursor does not follow and go to that range. This problem is only in one file that contains VBA code. I don't know whether I have mistakenly disabled that feature. I am using Excel 365. Please do reply, it is a pain for me.

[@ronnieboncodin509]

How to create data for clients for payments in lending?

[@petrusO-xu6uz]

Hi Kenji. I followed your instructions but the code doesn´t seem to work. It shows errors in the Dim paragraph from the code and some intermediate lines. I wonder if I am doing something wrong when pasting the code. Thanks in advance for your comments.

[@mmitchum3826]

I am also getting errors trying to use the VBA code. After i copy-paste the code, when I try to run the macro I get "Compile error: Syntax Error". The Dim rows, and many others, are in red text in VBA. I can retype the Dim lines, and it gets past the ones I retype. Not sure if there is a hidden char or something causing this.

[@agencycomp4849]

This is exactly what I wanted, however, your code breaks when I try to run it. I receive an error: Can't execute code in break mode. Any advise on how to fix this error?

[@willzinner8813]

thank you make mroe vba videos please

[@KenjiExplains]

noted!

[@pluto_dp]

It would be also nice to know how to do the opposite. I mean, starting from multiple sheets, aggregate data that present common columns. Maybe you have a video for that already?

[@KenjiExplains]

Yes I do here actually: kzbin.info/www/bejne/lZbHlmybatZ3f68

[@pluto_dp]

@@KenjiExplains TY Kenji 🙏🏻

[@keylanoslokj1806]

Problem with developer sheets is they are blocked by the IT teams of most corporations. So you are limited to a regular type sheet file without self injected code...

[@harshmudliar3737]

I am looking for something that's the exact opposite of this. I need to combine different Workbooks into one. its customers data. I have tried Power Query, but faced issues with managing the same customer having entries in different months.

[@akritigoel9497]

May be you should try consolidate if I am not wrong

[@mirash7]

Can you explain your problem with some additional details? I think in case you have some header problems, you can demote the headers of each page and then append all of them into a new query and then use that query.

[@pistonssssss]

All different data set must be converted to tables and must have at least one common column. Create coonection between them, than make a pivot from any of them, but mark “Add this data to data model”…

[@entrecapbusinessservices8329]

Use data consolidation in excel

[@krisongoh5169]

How about vstack function?

[@lydethful]

The 2nd method doesn't split data into different sheets.

[@igormajrov8444]

Not that kind of menu in Excel.

[@KenjiExplains]

What do you mean?

[@شعرکوتاه-ع7ظ]

Lik😂😂😂😊😊

[@2000sunsunny]

Thank you Kenji !