Split Excel Data into Multiple Sheets Automatically

Split Excel data into multiple worksheets automatically (3 methods).

In Excel, you often need to split data into multiple worksheets. For example, going from a total sales sheet, to splitting it by quarters into several sheets. So in this video I'm going to show you 3 ways you can split Excel data. Firstly, by using the report pages tool with Pivot Tables. Secondly, using the FILTER function and combining it with the VSTACK function to see the headers, and thirdly, with Macros and VBA (Visual Basic for Applications). With that, you'll be able to transfer excel data into multiple sheets in seconds!

LEARN:

SOCIALS:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ Method 1 (Simple)
3:17​ - Method 2 ( (Intermediate)
6:28​ - Method 3 (Advanced)

[carlosveterano]

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

[anjelas]

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.

[govardhanreddy]

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.

[keylanoslokj]

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...

[ronnieboncodin]

How to create data for clients for payments in lending?

[amitsalunkhe]

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

[navaki]

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.

[onadipemayokun]

Thank you so much Kenji..

I'm getting a syntax error on the code however, step 1 and 2 worked just fine😊

[harshmudliar]

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.

[vedalamganesh]

Hi Kenji,
Your each video is very much helpful and intuitive.... Thanks for the content, , , ,

Could you tell me the software you use to edit your videos

[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?