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.
Brilliant video. Thank you for that code it worked perfectly. You just save be 30 min per month splitting my sales rep data. Is there another step I can take to automatically make them individual single spreadsheets?
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
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.
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.
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.
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.
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?
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.
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.
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”…
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...
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?
🚀 FREE 50 Excel Hacks Template from Hubspot: clickhubspot.com/wcoo
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.
Brilliant video. Thank you for that code it worked perfectly. You just save be 30 min per month splitting my sales rep data. Is there another step I can take to automatically make them individual single spreadsheets?
Thank you deeply. It makes quite improve to my acquire skill.
God bless you brother ..! It was so helpful..!
Thank you Kenji!🙂
Thanks for watching :)
This is a great tip, thanks!
Glad it was helpful!
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
thank you so much i was thinking to try the same
This is amazing, you're wonderful!
awesome Kenji!
Thank you!
Great video!! Thanks
Glad you liked it!
Thank you so much Kenji..
I'm getting a syntax error on the code however, step 1 and 2 worked just fine😊
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.
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.
It is great. Thank you
Thanks for watching!
can these sheets be dynamic?
Do you have a video that rather than splitting it to worksheet, split it into workbook?
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
Is there a reason why I would not be able to choose show report filter under Pivot table analyze?
I was looking for something similar solution for google sheets. Currently I’m using query with filter but working on appscript to automate it
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.
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.
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.
The code VBA do not work. Error in "Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))".
How to create data for clients for payments in lending?
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?
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.
May be you should try consolidate if I am not wrong
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.
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”…
Use data consolidation in excel
How about vstack function?
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...
thank you make mroe vba videos please
noted!
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?
Yes I do here actually: ruclips.net/video/_ede9e5qDIM/видео.html
@@KenjiExplains TY Kenji 🙏🏻
The 2nd method doesn't split data into different sheets.
Not that kind of menu in Excel.
What do you mean?
Lik😂😂😂😊😊
Thank you Kenji !