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-anexcelexpert.com/vba-...
Visit to learn more:
Chart and Visualizations: www.pk-anexcelexpert.com/cate...
VBA Course: www.pk-anexcelexpert.com/vba/
Download useful Templates: www.pk-anexcelexpert.com/cate...
Dashboards: www.pk-anexcelexpert.com/exce...
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-AnExcelExpert.com
Facebook:
/ pkan-excel-expert-9748...
Telegram:
t.me/joinchat/AAAAAE2OnviiEk5...
Twitter:
/ priyendra_kumar
Pinterest:
/ pkanexcelexpert
Send me your queries on telegram:
@PKanExcelExpert

[@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!

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

[@alandouglas2789]

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

[@PKAnExcelExpert]

Thanks

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

[@JordanJens009]

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

[@sao8069]

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

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

[@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!

[@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!

[@narottammanihal8463]

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

[@lucasarancibia6397]

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

[@suki9860]

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

[@PKAnExcelExpert]

You are welcome!

[@sujeetsingh196]

Excellent. You saved my life in this lockdown

[@hanshusaini2930]

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

[@PKAnExcelExpert]

Glad, It helped you!

[@adnocbackup4986]

Excellent. Worked without any issues. Thank you very much

[@veruthe_oru_shashi]

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

[@DavidGomez-le7if]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback.

[@juliamartinez6021]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@aaravrajvlog111]

You really are "THE EXPERT". HANDS DOWN!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@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!

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

[@balrajvirdee1087]

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

[@PKAnExcelExpert]

Most welcome🙏

[@AmanUllah-yx5kg]

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

[@PKAnExcelExpert]

Great!

[@Yuthpanha]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@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🙏

[@magdikapinya7421]

Great Job! thank you for sharing it!

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

[@Ham_1982]

thank you for the tutorial, helped me a lot!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@TheMissSELane]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@chantell861]

I watched other videos but yours worked, thanks!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@info2satish]

Good stuff PK!👍

[@Sinoclarks]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@abhishekthakur4054]

Search finished at this point sir
Thanks a lot

[@Almirjorge1]

Show!!!
Thanks for sharing.

[@goldodubiyi680]

Thank you @PK. Great video.

[@PKAnExcelExpert]

Most welcome🙏

[@davidmvula4751]

Absolute brilliant work

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@vinayakmahajan1681]

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

[@vamshibandapally]

Great workbook. Very useful. Thank you

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@trevormeisel]

Excellent piece of work - thanks

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@amreshpatil6214]

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

[@PKAnExcelExpert]

Thanks

[@socratesa9277]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@tiwarikebi1]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

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

[@aldobrizuela8884]

muchas gracias muy buen aporte

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

[@guynganda1172]

THAT WAS VERY USEFULL THANKS ENDLESSLY

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

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

[@_Deepak2208]

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

[@PKAnExcelExpert]

Thanks Deepak for your valuable comments.

[@archanalenka9474]

A gem! Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@subhranilexcelc2732]

Very informative and helpful

[@mohamedaboobackersiddique7322]

thanks for your simple steps

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@anafonseca5359]

@PKanExcelExpert Thank you so much for this! One of my columns has a dropdown list with values to be selected and the dropdown options are disappearing in the new files created. Is there a way to keep them? Thanks!

[@manjunathks5082]

Very Helpful. Thank you so much

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@amritaagnihotri]

Really good stuff!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@onkarphadnavis]

Thanks for sharing.

[@karteekk6905]

Thank you very much !

[@PKAnExcelExpert]

Welcome

[@anooruproy3670]

Thanks it was very helpful.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@nazermelethil]

Thanks PK!

[@PKAnExcelExpert]

Most welcome

[@user-nm4ul6gr6o]

Thank you so much, Sir.

[@PKAnExcelExpert]

Most welcome

[@warlyndeguzman8761]

Finally, the code that works without any hassle!

[@PKAnExcelExpert]

good

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

[@sajeeshsasipm2430]

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

[@ORANGEPICTURESOFFICIAL]

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

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@hazemali382]

always great PK

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

[@tomirwin1453]

Thanks! How can you keep the sheet name (Data) the same when the new workbooks are created ?

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

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

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

[@yuchenzhou8355]

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

[@archie_2609]

very helpful video, really useful

[@PKAnExcelExpert]

Glad it was helpful!

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

[@michaelmohr7395]

Do you have any tips on how to maintain the a header or footer in the split files. In addition to this I am trying to add a signature box at the end of every file, footer of the last page? Appreciate the help.

[@buddhathakuri]

Wonderful great work

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@arindambiswas1463]

Very helpful... Thanks 🙏

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@AyeshDumindu]

superb

[@Run4itForever]

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

[@lucaaletti5255]

Awesome!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@anthonytrombetta2347]

Great video. However, does anyone know how to choose the columns that will be loaded in the new workbook (ex. columns A to K) and not have the header included?

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

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

[@shubham7564]

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

[@cwyau3525]

Been using your code and it's working perfect! is it possible to have the header as two row instead of one? what code should i use for that? many thanks!

[@craigvoss1468]

Great job

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@TanJunLe]

Hello, I have tried the code that you have shown in the video. However when I tried to open the individual file, there is no data in it. Please help. Thanks!

[@abhishekthakur4054]

Thanks sir.

[@ch1bob]

is there a way to separate into worksheets and not workbooks? basically I want a tab for each supervisor instead of a different file.
In the case of separating to another workbook is there a way to select only 1 supervisor to export? instead of exporting all of them when running the macro.

[@ggrocketleague9098]

You are a hero

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@AKILALIVE4U]

I have a requirement to create .csv excel files for each row in an excel sheet. Any suggestions?

[@avikvideosandshorts]

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

[@yingying178]

Super best!!!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@ajaykushwaha-je6mw]

You are genius!

[@PKAnExcelExpert]

Thanks🙏

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

[@bestguide6322]

Hi, PK, I have used macro and it is working fine but issue is that when new workbook create, it lose the Formulas which are given in master sheet. Can you help me to create new workbook with formulas?

[@apriliaaprilia9420]

Hey do you know why the 4th row : set data_sh = Thisworkbook.Sheets(“Data”) its say its wrong