Split one excel sheet into multiple sheets based on column value

Рет қаралды 93,075

Жыл бұрын

Split Data Into Multiple WORKBOOKS Based On Column Goto the below link:
• Split Data Into Multip...
Updated Weekly/Monthly Calendar for MS Excel
• Dynamic Appointment Sc...
Supposing you have a worksheet with huge rows of data, and now, you need to split the data into multiple worksheets based on the Name column and the names are entered randomly. Maybe you can sort them first, and then copy and paste them one by one into other new worksheets. But this will need your patience to copy and paste repeatedly. Today, I will talk about some quick tricks to solve this task. To know the ways of doing this job effectively let’s watch the video.
The above macro splits data into multiple worksheets based on the variables on a column found in Excel.
An InputBox asks you about "Header Names" then which columns you'd like to filter by, and it just creates these worksheets.
Download the file:
www.eloquens.com/tool/r94EC4w...
#excel #exceltips #exceltricks

Пікірлер: 112

@TheMKTube Жыл бұрын

Thanks for showing us you know how to do it 👏 Edit: For people who came to learn: I ended up creating a pivot table for the column I want to use to split into sheets. When you double-click the value of each category in the pivot, it actually launches a new Sheet with exactly the data that you need. No VBA required. Btw this approach won’t copy over hidden columns. Hope that helps!

@syedsahil7861 4 ай бұрын

Can we create pivot table as these ? Cause when am trying to create pivot table it gives us only numerical data/numbers in cells other than headings

@siddhantjain2126 Ай бұрын

Thanks for this. It really helped me in my job 😃

@nickmuckler1971 Жыл бұрын

This is exactly why they need to show us how many downvotes a video has

@raniafun9723 Жыл бұрын

This guy is made video only for himself not for others.

@quachanhvang Жыл бұрын

You reallly save my life bro! God bless you!

@ImproveExcel Жыл бұрын

Glad i could help.

@stellatriantafillou8733 Жыл бұрын

Thank you, that's very helpful!

@ImproveExcel Жыл бұрын

Glad it was helpful!

@sushantnarvekar5009 7 ай бұрын

How to create that button. You missed that part

@davidpraveen2111 5 ай бұрын

Your work was excellent.... saved a lot of time... exactly what I was searching for....awesome

@HaiderAli-qk9jy Жыл бұрын

Great information 👍🏻

@ImproveExcel Жыл бұрын

Glad you liked it

@madhujayaraman8630 Жыл бұрын

This is brilliant!!! This saved me 6hrs of work. I finished the report in 10 mins. Bro you are my lifesaver!!!

@ImproveExcel Жыл бұрын

Glad to hear.

@TheUKelvin Жыл бұрын

That's really awesome! Can you also share if we can export all the sheets in one workbook into different files?

@ImproveExcel Жыл бұрын

I will upload a video on this soon .

@ImproveExcel Жыл бұрын

You can see the video "Split Data Into Multiple WORKBOOKS Based On Column value" kzfaq.info/get/bejne/erqldsKryNiVh2w.html

@thidnascimento Жыл бұрын

Fantastic! Thank you so much! i had 11,000 rows with 81 values, 81 worksheets turn easy with your macro, thank you

@ImproveExcel Жыл бұрын

Gald to hear that.

@waytoham. Жыл бұрын

That's great 👍

@ImproveExcel Жыл бұрын

thanks

@kseplenhs Жыл бұрын

How do I delete the macro button from all the new sheets?

@brandedadnan Жыл бұрын

Thank you. it is so simple and very creative. Saved lots of time. can you include how the total sheets can be sent to create different workbooks.

@ImproveExcel Жыл бұрын

Absolutely! I will make an attempt to upload a video on this topic in the near future.

@ImproveExcel Жыл бұрын

You can see the video "Split Data Into Multiple WORKBOOKS Based On Column value" kzfaq.info/get/bejne/erqldsKryNiVh2w.html

@syedsahil7861 4 ай бұрын

Do that updates other sheets as well when we enter data in the main sheet ?

@yosephmatalo379 Жыл бұрын

Brother , Thanks for sharing but aren't gonna show us how you did it?

@ImproveExcel Жыл бұрын

Sure i will try to upload a new video that how to create this function.

@pritishsrivastav 3 ай бұрын

Nothing happens when I select the rows and columns. No new sheets, no error messages. Please tell me if there are any limitations, I've already reduced the column values to 30 characters.

@juniusangelcrisjunsay5302 3 ай бұрын

yeah, same here..

@eddynoboa1059 12 күн бұрын

when you say "click at this button here (00:58)", I dont see that button on my end

@abhaypratapsingh2234 Жыл бұрын

Hey i want to check this file can i see the VBA so that I'll use it according to my requirements

@ImproveExcel Жыл бұрын

www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@suryakanttikare6151 10 ай бұрын

Thanks a lot. Yours macro enabled file helped me to save lot of time. Can you please make a video how to create such macro enabled file. Thanks again.

@ImproveExcel 10 ай бұрын

Sure, i iwll.

@bufferingmusic 2 ай бұрын

Did you made a video ??

@callistakoh6098 Жыл бұрын

Hello, what is the VBAProjectPassword as i want to insert a module

@ImproveExcel Жыл бұрын

www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@siddeshkute5393 8 ай бұрын

Hello, If there are multiple sheets in a base data workbook that are formula linked with each other, is there any way to create multiple workbooks from the base workbook based on a differentiator? Please let me know if this is possible with the help of macro.

@ImproveExcel 8 ай бұрын

Yes, you can create multiple workbooks from a base workbook with formula-linked sheets using VBA in Excel. The differentiator could be a value in a specific column or any other criteria that distinguishes the data for each workbook.

@lexismith1687 Жыл бұрын

Now is there a way to also combine all of the sheets back into one table? This macro is a huge help to me and I use it daily!

@ImproveExcel Жыл бұрын

Yeah there is a way of doing this.

@varishadar8812 Ай бұрын

Will it also work if for some columns we have some blank cells?

@ImproveExcel Ай бұрын

it won't work on blank cells.

@marclink0 Жыл бұрын

Exactly what I am looking for ! I have around 120 files with over 1,500 rows in each. I was able to combine them in a single excel sheet (around 150k rows), with file name in first column. However, following your steps I am not able to split them into individual sheets. Instead, excel creates a new sheet with only the headers. Could you help me or is my data simply too big?

@marclink0 Жыл бұрын

Update : The macro works until it breaks somewhere beyond 3000 rows. Could it be optimized, since my data are ordered logically from top to bottom, instead of being random like in your example?

@ImproveExcel Жыл бұрын

Can you please share your data at Email: SheetsandExcel@gmail.com

@howtonotbefunny9560 Жыл бұрын

This is only working if you do this to your data at the end, what if I want to have a sheet that keeps updating after every entry?

@ImproveExcel Жыл бұрын

No worries! Even if your sheets keep updating the data, simply run the code and specify the relevant column and row for extracting the data. The code will still work effectively.

@tarikgahlouz5467 Жыл бұрын

Thanks this is brilliant ! Can you please share the password as I'd like to apply it on one of my work sheet, thanks

@ImproveExcel Жыл бұрын

www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@mrbdent 3 ай бұрын

What is the "click on this button"? Excel does not come with this button

@ImproveExcel 3 ай бұрын

You're right. I have added a code attached with this button to perform specific action.

@abduljaleelpanthakkan6942 Жыл бұрын

If you add a new column in the split excel sheets, what should you do to get to the main sheet?

@ImproveExcel Жыл бұрын

I'm sorry i really do not understand your question.

@CrishelleBalingit 2 күн бұрын

Where do you find the " Split Data into Worksheets Based on Column" Button?

@ImproveExcel Күн бұрын

Once you download the file from the link provided in the description of the video, you will see that the button is already available with the required code to perform this task.

@eddynoboa1059 12 күн бұрын

where is that button ...you missed that part??????

@sethdanielfernandez1239 10 ай бұрын

Can you show us how did you create this MACRO button? I also wanted to learn how to create this.

@ImproveExcel 9 ай бұрын

I will try to upload a video soon on this.

@sethdanielfernandez1239 9 ай бұрын

@@ImproveExcel Thank you. I look up the code and Its kinda complicated and advance 😅

@napoleonmoscosa9353 Жыл бұрын

What? How do i get that button , you didnt even show us

@ImproveExcel Жыл бұрын

Link to sownload the workbook: kzfaq.info?event=video_description&redir_token=QUFFLUhqazR2MWU2MEM3eFdtRXZpd0xLV2Y2eXp5SkxqZ3xBQ3Jtc0trSUN5ZkQ4MGFKUWh0STJNb3hEV2JENThyT3c1NHRwaGxxblZScnZQaXNidVhpZEIzU1JSbGRtTm1UaF9WeWxFQlRGUmRhRF9OcGk1dERzQUlsamVPN1RmZnRYQThsbFFuNkdmN1VFbWJCT3hOSkNIMA&q=https%3A%2F%2Fwww.dropbox.com%2Fs%2F8ouxhdvuk7scf56%2FSplit%2520Data%2520Into%2520Multiple%2520Worksheets%2520Based%2520On%2520Column.zip%3Fdl%3D0&v=XcECVeqztRk

@darajabar93 3 ай бұрын

It keeps giving error. And when I try to assign it, there is nothing to assign it to.

@ImproveExcel 2 ай бұрын

I have verified that everything is functioning properly. Could you please provide me with details about the error so that I can investigate and resolve it?

@hafiztariq9030 7 ай бұрын

Where to find this Button "Split Data into multiple Sheets". Unique Function is not working in Excel 2016?

@ImproveExcel 7 ай бұрын

Did you turn on the macros before opening the workbook.

@akashkumar-pr3yi Ай бұрын

Go to his description he has given a downlode link of the excel file

@funtoadebayo4892 7 ай бұрын

Please how do we get this macro?

@ImproveExcel 7 ай бұрын

The macro is conveniently accessible in the workbook, for which the link is provided in the description of this video.

@jorgepacheco6084 10 ай бұрын

How do I get this macro

@ImproveExcel 10 ай бұрын

Macro is avaialble in the Excel file which link is available in the video description.

@BABIRYEPRISCILLA Жыл бұрын

The yellow button is not on my laptop,please help

@ImproveExcel Жыл бұрын

I really do not know why it is happening.

@kingfighter08 4 ай бұрын

But question is how to do

@NEyXZU 10 ай бұрын

Hi. I saw the VBA is locked with password. Can I have the password to overwrite it?

@ImproveExcel 10 ай бұрын

Please find the attached file www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@cctvperjalanan8991 Жыл бұрын

please Share your make VBA Project dont your Password. help please. thankyou sir

@ImproveExcel Жыл бұрын

Workbook without password www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@shariqmohd4067 Жыл бұрын

how you have created this yellow button "Split Data Into Multiple Worksheets Based on Column"

@ImproveExcel Жыл бұрын

Creating this button is quite straightforward. I've linked it to an Excel-VBA code that separates the sheets based on column values.

@user-qx5ce1vq8b 7 ай бұрын

How to get that split data into multiple sheets bar

@ImproveExcel 7 ай бұрын

The button is already available in the workbook, which link is attached below to this video in the description.

@reenapatel614 Жыл бұрын

From where do we find that yellow tab..slipt data into multiple worksheets based on column

@ImproveExcel Жыл бұрын

You can see the code's execution instructions by visiting this link. rb.gy/fwmged

@pojones92 Жыл бұрын

This does not work for separating by date

@ImproveExcel Жыл бұрын

Its not.

@YakoubiAdnen Жыл бұрын

Why you didn't share the Macros Password ?

@ImproveExcel Жыл бұрын

www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@chandrakanthpawar5205 23 күн бұрын

do for others

@santanuroy571 Жыл бұрын

Its very basic.. show us when there is more than 2 millions of record then how can we split specially excel has only displays 1048764 rows...

@pulkitupadhyay6817 9 ай бұрын

Bhai ye kese krte he vo btana tha....this means nothing.

@ankitgupta8677 Жыл бұрын

Can you tell us what is password of vba code

@ankitgupta8677 Жыл бұрын

And they are only split data into sheet but can i split into new workbook

@ImproveExcel Жыл бұрын

@@ankitgupta8677 www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@ankitgupta8677 Жыл бұрын

Split data into new workbook not worksheet

@ankitgupta8677 Жыл бұрын

@@ImproveExcel can you share password of vba code

@ImproveExcel Жыл бұрын

@@ankitgupta8677 this link i have attached above in the chat doesn't contain the password.

@dcarterrhoden2884 Жыл бұрын

Did anyone get the password??

@ImproveExcel Жыл бұрын

www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@user-ws7ej9lp5k Жыл бұрын

what is the password please?

@ImproveExcel Жыл бұрын

www.dropbox.com/scl/fi/f72qnab218p1l2vmmdbqu/Split-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm?dl=0&rlkey=s6d7gim27d78osq2tosl2zayl

@ianyoung1101 11 ай бұрын

Just give us the code?!?!??!?

@ImproveExcel 11 ай бұрын

The code can be found within the file, and a link is provided in the description for your reference.

@jamilurrehman2754 Жыл бұрын

Can you please share the password of the file?

@jamilurrehman2754 Жыл бұрын

I want to separate each sheet into multiple workbooks, that's need vba code, but while updating vba code this file is password protected.

@ImproveExcel Жыл бұрын

@@jamilurrehman2754 without password kzfaq.info?event=comments&redir_token=QUFFLUhqbGp1Mks1YXJXeXZ1OHBERGhBOTQtWHhCa2hyUXxBQ3Jtc0trWWc2SVdPQXd4RGFvY1FpVGVMdUVUWnNwbVl1Z004N2lzcHI3UEtMbEFTclh3Nm5NYmhBa1dXUEs1N2l2dGpuNjhmSG5qd1AzdzAwQ3ItNzNIZjZfenJnZzY1Ny1LMXFCQTN2ckdwRHdUd0h5ZTA0TQ&q=https%3A%2F%2Fwww.dropbox.com%2Fscl%2Ffi%2Ff72qnab218p1l2vmmdbqu%2FSplit-Data-Into-Multiple-Worksheets-Based-On-Column-V_2.xlsm%3Fdl%3D0%26rlkey%3Ds6d7gim27d78osq2tosl2zayl&stzid=UgxGSWlGT7csmgQR1cB4AaABAg.9iBqfWbd6kC9iCYt9Pf5LQ