VBA Macro: Split Excel Worksheet into Multiple Worksheets Based on Column Value

In this Microsoft Excel video tutorial I demonstrate my VBA macro that will split data, currently held in a single worksheet, across multiple worksheets based on a column value. You can download the feature file or copy the code by visiting the page linked to above.

00:00 Introduction
00:18 See the macro in action
01:38 Walk through of the VBA macro code
06:40 Assign a button (on the Ribbon) to run the macro

[KWhite-ivse]

Truly Amazing! Very helpful, this was exactly what I was looking for. You saved the day for me.

[rachitmittal]

Thank you so much for this. Saved so much time

[stefapaco]

Did all the steps but for me it only creates a new sheet but doesnt populate it with anything

[kimbereleybrown]

Hi Chester. Great video and explanation of the code. Thank you.

[irfankhanuni]

Hi chester, loved this video, it worked for me, thanks

[mattm]

Great video. I was also wondering if there was a way to update the code to include blank cells.

[kevj]

Great video, unfortunately when i run the macro, it copies the headers across for the first new worksheet but then does not populate any data in the rows below and I have no other sheets created

[kimbui]

Hi Chester, loved the video. Is there a way to modify your VBA code so it works for blank cells as well?

[tomgraham]

great tool and explanation although macro doesn't copy all of my requested data - only some of the rows and some of the columns, missing critical data - any clues how to fix?

[eddynoboa]

Hello Chester, I did use your code and it worked using very small data and thanks for that. In the other hand, with big data have 24 columns and lots of data when I used your code only picked up 13 columns and based on the column value to pick up on (had 120 unique accounts) it only picked up around 5 or 6. How can I pick all unique accounts with the 24 columns??

[alexandralerebours]

Hi Chester, It's awesome. Works great! Do you have a vba macro code that allows to send each of the worksheets to email address that would be located in a column of that worksheet?

[whokmot]

This is great Chester. Is there a way to modify this macro to retain the formulas on each tab?

[whokmot]

This is great Chester. How can I modify this macro to keep the formulas on each new sheet created?

[wnxobmu]

Really useful video. The column I want to split the data by is a date field, which the macro doesn't recognise unless I remove the date formatting. I then need to convert the data in each tab back to a date. Is there anyway to have the macro recognise dates? Thank you.

[sarahmosaad]

Is there a way to modify your VBA code so it works for blank cells as well?

[JoeMcMullin]

Absolutely love this script, one issue. When I run it as a macro, it runs perfect. When I add the macro to a button on the ribbon it runs and only creates one new tab/worksheet with only two rows of data (should be many more) but the script just stops. Not sure why there would be a difference. I am using Office RibbonX Editor to create my own menu group and have no issues with other scripts. Thoughts or anyone experience this problem?

[dragonfly]

I am new to VBA Macro, but is there a way to do everything you have on the macro, but sort by the first two or three chars in a cell row? For Example Cell A-000, sort by just the A- in the cell?

[NilishaKothari]

It didn’t work for me. I have bigdata and have to split into around 80 sheets. Any idea how?

[ricardopedraza]

Hey Chester, amazing coding, how to add a total in column "E" ?

[gamingindia]

Is there any way in which column copy past even if there are blank cells?