VBA: Split data into Separate Workbooks

Hello Friends,
In this video you will learn how we can split our data into multiple workbooks. Every workbook will be saved in the given folder path. This macro can be used when you have a long list of your data and you have to make different files with specific data. Here in this example we have taken Employee wise performance data and we have to split our data for each supervisor.
Download the excel file from below given link:
www.pk-anexcel...
Visit to learn more:
Chart and Visualizations: www.pk-anexcel...
VBA Course: www.pk-anexcel...
Download useful Templates: www.pk-anexcel...
Dashboards: www.pk-anexcel...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
www.PK-AnExcel...
Facebook:
/ pkan-excel-expert-9748...
Telegram:
t.me/joinchat/...
Twitter:
/ priyendra_kumar
Pinterest:
/ pkanexcelexpert
Send me your queries on telegram:
@PKanExcelExpert

[@TheMissSELane]

Hi All
Here’s how to Auto Fit column width:
Where he has typed “ColumnWidth = 15”, replace it with “AutoFit” ☺️ it just worked for me.

[@RawInsanity]

This was exactly what i needed. i spent hours sleuthing the microsoft support pages before stumbling across this video. thank you thank you thank you!!!!

[@PKAnExcelExpert]

Glad it helped!

[@bcnicholas123]

You just saved me a week of work with a single click. Thanks

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@Johnck90]

You have literally just saved me a heap of work, and reduced any chance of error! Absolutely amazing!! Thank you

[@PKAnExcelExpert]

Glad it helped!

[@techtip4u888]

I updated vba code based on my requirements and achieved goal successfully with your support. Thanks PK for your support.

[@nohoescall]

I love you! thanks. It costed me hours to understand. First time in my life using Visual, but it was worth it

[@PKAnExcelExpert]

Glad it helped!

[@aaravrajvlog111]

You really are "THE EXPERT". HANDS DOWN!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@lucasarancibia6397]

I came for help and I found glory haha. Thank you so much, my friend!!!

[@CB-xk6ce]

You rock man, saved me 3 days of work. Thanks!

[@PKAnExcelExpert]

Glad I could help!

[@nitrush2531]

Hi. Getting this errors

[@nitrush2531]

Run time error 13 type mismatch on
Set nsh=nwb.sheets line.
Could you please help

[@alandouglas2789]

What an amazingly helpful video. Short code, easy to see how it works

[@PKAnExcelExpert]

Thanks

[@JordanJens009]

Excellent work and thank you for such a clear line by line explanation!

[@narottammanihal8463]

अति सुन्दर , P K Sir, मज़ा आ गया आपके VBA Code से , एक दम Magic... So Nice Sir.......

[@jaironaranjo3654]

Honestly i don't know which one of your videos is the best every time i watch any of you videos including Power Bi I said to myself "no way this one is the best". In conclusion Thank you for what you are doing you are definitely the best!

[@AmanUllah-yx5kg]

Thank you bro I have split data into 237 separate files by this amazing technique.

[@PKAnExcelExpert]

Great!

[@suki9860]

Absolutely excellent and perfect solution. Thank you very much, PK! !

[@PKAnExcelExpert]

You are welcome!

[@FalconFlyer75]

I cant thank you enough, my work asked me to do this, I was clueless on how to go about it, u just saved me

[@DavidGomez-le7if]

This is life saver, I might need to use this for several hundreds of worksheets.

[@PKAnExcelExpert]

Thanks for your valuable feedback.

[@sao8069]

Amazing video, I expected this task to take me a few hours but thanks to you it took 45 minutes. Thanks so much!

[@franciscogarcia7280]

Great video. It is just what I was looking for to automate some data processing. Thank you for sharing your knowledge.

[@PKAnExcelExpert]

Glad it was helpful!

[@bharathipuduru3684]

Hi, It is very helpful for my reports, thanks a lot for providing such a great tool, it is saving my lot of time.

[@PKAnExcelExpert]

Glad it helped

[@sujeetsingh196]

Excellent. You saved my life in this lockdown

[@chantell861]

I watched other videos but yours worked, thanks!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@hanshusaini2930]

You saved a lot of my time. Very well explained. Thank You

[@PKAnExcelExpert]

Glad, It helped you!

[@munish1246]

Thank you so much for the short yet helpful video, its save more time

[@PKAnExcelExpert]

Glad it helped

[@joshuafields8685]

Just an outstanding tutorial! Exactly what I needed.

[@PKAnExcelExpert]

Thanks for your valuable feedback🙏

[@veruthe_oru_shashi]

Dear Friend,
Thanks for sharing this. You are saving tons of time for people

[@Yuthpanha]

Thank you so much Mr.PK for your useful sharing.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@Sinoclarks]

Thankyu so much sir for these kind of Tutorials... I really liked your work

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@balrajvirdee1087]

Thanks so much for sharing this, life saver!!!

[@PKAnExcelExpert]

Most welcome🙏

[@goldodubiyi680]

Thank you @PK. Great video.

[@PKAnExcelExpert]

Most welcome🙏

[@socratesa9277]

very good explanation . i thank pk for outstanding and commendable work in excel.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@mukuljainn]

This was the output I was searching from a long time. Thanks @PK: An Excel Expert
Also, I was willing to know that How the same output can be obtained using Advanced Filter instead of an AutiFilter ?
Using an Advanced Filter will save a good amount of loading time of copy & paste. Your support is Valuable. Thanks

[@vijay_Excel]

Thanks alot for sharing woderful tutorials really It's a very useful have saved 3 hars in a day.....

[@archie_2609]

very helpful video, really useful

[@PKAnExcelExpert]

Glad it was helpful!

[@juliamartinez6021]

Thank you so much for the short yet helpful video, I did it :)

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@adnocbackup4986]

Excellent. Worked without any issues. Thank you very much

[@arindambiswas1463]

Very helpful... Thanks 🙏

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@nazermelethil]

Thanks PK!

[@PKAnExcelExpert]

Most welcome

[@amreshpatil6214]

Thanks pk this video was reduce by time to work. Once again thanks

[@PKAnExcelExpert]

Thanks

[@tiwarikebi1]

Wahooo!! Great!! Thank you very much !!! It's done

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@manjunathks5082]

Very Helpful. Thank you so much

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@MohAboAbdo]

Thank you so much, Sir.

[@PKAnExcelExpert]

Most welcome

[@TheMissSELane]

Amazing 😊 it worked so well. Thank you so very much!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@abhishekthakur4054]

Search finished at this point sir
Thanks a lot

[@sub1994hash]

Thanks man, got my job done.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@sub1994hash]

@@PKAnExcelExpert can u tell what the code should be if i want to run the macro on the same active worksheet. The code should run on the current worksheet without asking the name of worksheet.

[@PKAnExcelExpert]

You can use "Activesheet" key word. For example Activesheet.range("A1:A10").Select

[@joannasteinweiner7246]

Thank you for the video - would you mind answering a few questions? If I have multiple header rows I want to maintain how do I need to amend the code and is there a way to maintain formulas in the new worksheets? Thanks again

[@archanalenka9474]

A gem! Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@vamshibandapally]

Great workbook. Very useful. Thank you

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@ajaykushwaha-je6mw]

You are genius!

[@PKAnExcelExpert]

Thanks🙏

[@ggrocketleague9098]

You are a hero

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@pljjclaes5201]

Hello PK, finally I have found the explanation that I have been looking for, for so long. And it works like a charm. I have even made a number of additions such as Freeze Top Row & Autofilter, but what I can't do is that Excel takes the column width 'exactly' as in the original file. Autofit comes close, but it just isn't. Do you have a solution for this? Then I really would have the perfect excel file and this saves me days of work. Thank you very much in advance.

[@TheMissSELane]

Yes that is exactly what I need too, keep the column width as per original file 😅. If it isn't too much trouble, might you please explain how you managed to include Freeze Pains & Autofilter? ☺

[@avikvideosandshorts]

This is very helpful
One query: how to add sensitivity label while saving the newly created file.

[@trevormeisel]

Excellent piece of work - thanks

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@satishanagesh6755]

This macro helped me lot. However, How we can split two sheets data (Example Data1 and Data 2) into multiple workbook with unique name

[@aashraynalawade]

Me too looking for this solution. How to split the data of there are more than 1 sata sheet.

[@davidmvula4751]

Absolute brilliant work

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@girishsign007]

Superb Representation and it's working well.. I saved 6 hours of time for every month.. Along with this can you please explain how to protect all these file with password using VBA?

[@sudheeshtr]

Nice video....thank u.....

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@rupeshpatel9626]

Thank you, this helped tremendously, was able to change a few things to what was needed. Question: When saving the individual files you have set it to Supervisor-1 and so on, what if I need to save each file with added text to "Supervisor-1" such as "XXXX-XXX-Supervisor-1"?

[@karthik9038]

Hello, thank so much for the tutorial most of your videos was really helpful and problem solving. I have one question, is it possible to copy only specific column for the database to an another sheet. For example in the above video have to make sheets in name of supervisor with only column name B,C & E. How to do this , please let me know
Thanks in advance. 👍👍

[@sajeeshsasipm2430]

in this tool required above 255 character drop down from another sheet
Tool is good and useful

[@rochmajungreis9962]

hi
i spent over 65 hours trying to split a worksheet master into worksheets filtered by column B, AND workbooks at the same time
1- to be updatable every time macro is run, delete old info and rewrite
2- to divide each workbook filtered by column E

[@abbeyshaw3975]

This worked perfectly! Huge time saver! Does anyone know if/How I can add a summation to a column after each workbook is created?

[@_Deepak2208]

Thanks thanks thanks & thats a ton 😊 it's really useful ..👌

[@PKAnExcelExpert]

Thanks Deepak for your valuable comments.

[@warlyndeguzman8761]

Finally, the code that works without any hassle!

[@PKAnExcelExpert]

good

[@ashtonaguiar4967]

Great. It works cool. Big help. Thanks

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@ashtonaguiar4967]

Only need to know one thing please. If I need to save the files with extn .xlsb what do I do please?

[@mohamedaboobackersiddique7322]

thanks for your simple steps

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@sidikys]

Thank you PK, however i have a question, what if you want to use two supervisors in one work book. lets say Supervisor-1 and Supervisor-4 combine in one work book and the rest can be separated. how would you do that? Please help, i am working on a project and i have multiple data that i want in one work book. Thank you

[@hazemali382]

always great PK

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@parthabhatta1963]

An excellent tutorial of VBA for a very commonly used Excel job. This will cut short the time by many minutes / hours as per the data size.,👍👍👍👍👍. Thanks for that.
I have one question, Will the "Remove Duplicate" command work on unsorted data in "Setting" sheet? Since your data was pre-sorted, the command worked well. If the command does not work on unsorted data, a sort command, before the "Remove Duplicate" may be necessary. Please mention that command syntax since I am not conversant with VBA.
Thanks again.

[@Ham_1982]

thank you for the tutorial, helped me a lot!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@ishansharma9059]

I have a workbook with 4 product categories A, B , C and D and code assigned to each item is preceding with category like A01, A03, B05, B05, C32, etc. I need to create separate workbooks for product category A, B, C and D. In each Workbook, separate sheets for each A cod like ( A01, A02, A03 etc) and sheets should be name as per code, with data in sheets corresponding to particular code

[@karteekk6905]

Thank you very much !

[@PKAnExcelExpert]

Welcome

[@aashraynalawade]

I'ma big fan of yours pk, I have also installed ur pk utilities add-on.its just the best tool I ever used in Excel. This tutorial is simply awesome. I just had one more requirement like this example there was one sheet from which it sorted supervisor n made new sheet. If there are more than 1 sheet with say similar supervisor names...will copy it make new workbook and save with data of the same supervisor from more than one sheet? Please help.

[@PKAnExcelExpert]

Hi Kiran,
I will definitely try to make a video on this topic very soon.

[@aashraynalawade]

@@PKAnExcelExpert thank you very much sir

[@yingying178]

Super best!!!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@anooruproy3670]

Thanks it was very helpful.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@apsadmin4524]

Thank you very much for this video,
How to split data with title and table header?
Ex: A1 - Title(Student details from Nov - Dec 2021), A2 - (Date :dd/mm/yyyy), A3 - row header (S.No., Name, Age, Date) Thank you

[@subhranilexcelc2732]

Very informative and helpful

[@VinayakSMahajan]

thank you very much.....very nicely explained......!!!!

[@shubham7564]

Thank you, Shouldn't we set screen updating to TRUE at the end of the code?

[@amolparab6247]

Nicely explained

[@PKAnExcelExpert]

Thanks for liking

[@alarkgujarathi3120]

Hi Thank you for the video just had a question what can be do if i have 10 sheets data and create a splits as per name , you have one sheet data only i have 10 sheets data and need the same format of old file

[@guynganda1172]

THAT WAS VERY USEFULL THANKS ENDLESSLY

[@Reduce_Scan]

Thank you for the Tutorial, I have Question please How can insert Value in cell A5 for example in multi open workbooks not worksheets ,
every cell in multi open workbooks in the same name of worksheets

[@pankajjaswani9155]

Hi PK, Please make a video on multiple worksheet's split data into separate workbooks. I need your help with this.

[@Run4itForever]

Would it be possible to add to the macro instructions to email the individual .xlsx files to specified email addresses?

[@magdikapinya7421]

Great Job! thank you for sharing it!

[@buddhathakuri]

Wonderful great work

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@raliyev]

Thank you for sharing. Could you please advise how to automatically clear the filter on data page after an imlementation.

[@PKAnExcelExpert]

You can use sheetname.autofiltermode=false

[@Vikrantw810i]

Very useful video, only challange I am facing files are getting created but data row is blank.
Headders are correct but data is not promoting.
Will you please help.
Thank you in advance

[@amritaagnihotri]

Really good stuff!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@abhishekthakur4054]

Thanks sir.

[@smasthan1628]

Thanks for excellent video, example if i want to filter supervisor 1 to 3, 4-6 etc how to change the vba code?

[@wendywendy4322]

Hi PK, this is very useful video. Can i ask, how to write the code if i want to split the files by sales value? Example Sales value above $10k into one workbook, above $50k one workbook and etc.?

[@nikhilmehta419]

I want to split data into different worksheets in same file and not new workbook as done in this video, please advise

[@PKAnExcelExpert]

Will create a video on this topic

[@yuchenzhou8355]

thanks for sharing! another question, how to split into multiple workbook based on row count?

[@info2satish]

Good stuff PK!👍

[@supermariochannel4182]

Thanx Bhai

[@PKAnExcelExpert]

Welcome

[@rajeshramesh3636]

Question : In Macro file, when I split the excel and saving in .xlsx using Macro.
But I want to save in Binary format .xlsb and I tried as well but it won't work for me...
Pls guide me how to save binary format in Macro splits excel?

[@ORANGEPICTURESOFFICIAL]

Nice video sir.....very useful video. Thank you

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@lucaaletti5255]

Awesome!

[@PKAnExcelExpert]

Thanks for your valuable feedback