VBA: Split data into Separate Workbooks

Рет қаралды 103,256

6 жыл бұрын

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

Пікірлер: 340

@TheMissSELane 3 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

Glad it helped!

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

@bcnicholas123 5 жыл бұрын

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

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@JordanJens009 4 жыл бұрын

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

@sao8069 3 жыл бұрын

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

@FalconFlyer75 3 жыл бұрын

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 2 жыл бұрын

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!

@narottammanihal8463 4 жыл бұрын

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

@techtip4u888 2 жыл бұрын

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

@suki9860 2 жыл бұрын

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

@PKAnExcelExpert 2 жыл бұрын

You are welcome!

@lucasarancibia6397 3 жыл бұрын

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

@nohoescall 3 жыл бұрын

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

@PKAnExcelExpert 3 жыл бұрын

Glad it helped!

@sujeetsingh196 4 жыл бұрын

Excellent. You saved my life in this lockdown

@joshuafields8685 2 жыл бұрын

Just an outstanding tutorial! Exactly what I needed.

@PKAnExcelExpert 2 жыл бұрын

Thanks for your valuable feedback🙏

@adnocbackup4986 3 жыл бұрын

Excellent. Worked without any issues. Thank you very much

@hanshusaini2930 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Glad, It helped you!

@aaravrajvlog111 Жыл бұрын

You really are "THE EXPERT". HANDS DOWN!

@PKAnExcelExpert Жыл бұрын

Thanks for your valuable feedback

@CB-xk6ce 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Glad I could help!

@nitrush2531 3 ай бұрын

Hi. Getting this errors

@nitrush2531 3 ай бұрын

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

@veruthe_oru_shashi 4 жыл бұрын

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

@juliamartinez6021 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@Yuthpanha 5 жыл бұрын

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

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@AmanUllah-yx5kg Жыл бұрын

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

@PKAnExcelExpert Жыл бұрын

Great!

@franciscogarcia7280 2 жыл бұрын

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

@PKAnExcelExpert 2 жыл бұрын

Glad it was helpful!

@alandouglas2789 3 ай бұрын

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

@PKAnExcelExpert 3 ай бұрын

Thanks

@magdikapinya7421 5 жыл бұрын

Great Job! thank you for sharing it!

@munish1246 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Glad it helped

@DavidGomez-le7if 11 ай бұрын

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

@PKAnExcelExpert 11 ай бұрын

Thanks for your valuable feedback.

@TheMissSELane 3 жыл бұрын

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

@PKAnExcelExpert 3 жыл бұрын

Thanks for your valuable feedback

@Ham_1982 5 жыл бұрын

thank you for the tutorial, helped me a lot!

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@info2satish 6 жыл бұрын

Good stuff PK!👍

@vijay_Excel 3 жыл бұрын

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

@chantell861 4 жыл бұрын

I watched other videos but yours worked, thanks!

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@vamshibandapally 6 жыл бұрын

Great workbook. Very useful. Thank you

@PKAnExcelExpert 6 жыл бұрын

Thanks for your valuable feedback

@abhishekthakur4054 2 жыл бұрын

Search finished at this point sir Thanks a lot

@mukuljainn 4 жыл бұрын

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

@trevormeisel 5 жыл бұрын

Excellent piece of work - thanks

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@balrajvirdee1087 Жыл бұрын

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

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@vinayakmahajan1681 5 жыл бұрын

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

@goldodubiyi680 Жыл бұрын

Thank you @PK. Great video.

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@davidmvula4751 4 жыл бұрын

Absolute brilliant work

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@tiwarikebi1 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@amreshpatil6214 5 жыл бұрын

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

@PKAnExcelExpert 5 жыл бұрын

Thanks

@aldobrizuela8884 3 жыл бұрын

muchas gracias muy buen aporte

@socratesa9277 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@Almirjorge1 6 жыл бұрын

Show!!! Thanks for sharing.

@joannasteinweiner7246 3 жыл бұрын

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 5 жыл бұрын

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

@PKAnExcelExpert 5 жыл бұрын

Thanks Deepak for your valuable comments.

@Sinoclarks 4 жыл бұрын

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

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@mohamedaboobackersiddique7322 5 жыл бұрын

thanks for your simple steps

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@anooruproy3670 5 жыл бұрын

Thanks it was very helpful.

@PKAnExcelExpert 5 жыл бұрын

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

@subhranilexcelc2732 4 жыл бұрын

Very informative and helpful

@guynganda1172 3 жыл бұрын

THAT WAS VERY USEFULL THANKS ENDLESSLY

@manjunathks5082 3 жыл бұрын

Very Helpful. Thank you so much

@PKAnExcelExpert 3 жыл бұрын

Thanks for your valuable feedback

@amritaagnihotri 4 жыл бұрын

Really good stuff!

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@ORANGEPICTURESOFFICIAL 6 жыл бұрын

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

@PKAnExcelExpert 6 жыл бұрын

Thanks for your valuable feedback

@ashtonaguiar4967 3 жыл бұрын

Great. It works cool. Big help. Thanks

@PKAnExcelExpert 3 жыл бұрын

Thanks for your valuable feedback

@ashtonaguiar4967 3 жыл бұрын

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

@anafonseca5359 3 жыл бұрын

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

@rupeshpatel9626 3 жыл бұрын

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

@abbeyshaw3975 2 жыл бұрын

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

@buddhathakuri 5 жыл бұрын

Wonderful great work

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@sub1994hash 5 жыл бұрын

Thanks man, got my job done.

@PKAnExcelExpert 5 жыл бұрын

Thanks for your valuable feedback

@sub1994hash 5 жыл бұрын

@@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 5 жыл бұрын

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

@hazemali382 4 жыл бұрын

always great PK

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@archanalenka9474 Жыл бұрын

A gem! Thanks

@PKAnExcelExpert Жыл бұрын

Most welcome🙏

@karteekk6905 3 жыл бұрын

Thank you very much !

@PKAnExcelExpert 3 жыл бұрын

Welcome

@sidikys 4 жыл бұрын

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

@arindambiswas1463 2 жыл бұрын

Very helpful... Thanks 🙏

@PKAnExcelExpert 2 жыл бұрын

Thanks for your valuable feedback

@archie_2609 2 жыл бұрын

very helpful video, really useful

@PKAnExcelExpert 2 жыл бұрын

Glad it was helpful!

@onkarphadnavis 3 жыл бұрын

Thanks for sharing.

@MohAboAbdo 4 ай бұрын

Thank you so much, Sir.

@PKAnExcelExpert 4 ай бұрын

Most welcome

@sajeeshsasipm2430 4 жыл бұрын

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

@nazermelethil Жыл бұрын

Thanks PK!

@PKAnExcelExpert Жыл бұрын

Most welcome

@cwyau3525 5 жыл бұрын

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!

@tomirwin1453 4 жыл бұрын

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

@warlyndeguzman8761 4 жыл бұрын

Finally, the code that works without any hassle!

@PKAnExcelExpert 4 жыл бұрын

good

@craigvoss1468 6 жыл бұрын

Great job

@PKAnExcelExpert 6 жыл бұрын

Thanks for your valuable feedback

@Run4itForever 4 жыл бұрын

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

@Reduce_Scan 4 жыл бұрын

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

@pljjclaes5201 4 жыл бұрын

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 2 жыл бұрын

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

@girishsign007 3 жыл бұрын

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?

@yuchenzhou8355 4 жыл бұрын

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

@lucaaletti5255 4 жыл бұрын

Awesome!

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@michaelmohr7395 4 жыл бұрын

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.

@amolparab6247 4 жыл бұрын

Nicely explained

@PKAnExcelExpert 4 жыл бұрын

Thanks for liking

@parthabhatta1963 4 жыл бұрын

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.

@sudheeshtr 3 жыл бұрын

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

@PKAnExcelExpert 3 жыл бұрын

Thanks for your valuable feedback

@ggrocketleague9098 3 жыл бұрын

You are a hero

@PKAnExcelExpert 3 жыл бұрын

Thanks for your valuable feedback

@shubham7564 Жыл бұрын

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

@yingying178 4 жыл бұрын

Super best!!!

@PKAnExcelExpert 4 жыл бұрын

Thanks for your valuable feedback

@ajaykushwaha-je6mw 2 жыл бұрын

You are genius!

@PKAnExcelExpert 2 жыл бұрын

Thanks🙏

@SirPattyOfCakes 4 жыл бұрын

Is there a way to make these individual files save as Macintosh Comma Separated (.csv) ??

@sudheeralle 6 жыл бұрын

Good video.

@PKAnExcelExpert 6 жыл бұрын

Thanks for your valuable feedback

@bestguide6322 5 жыл бұрын

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?

@AyeshDumindu 4 жыл бұрын

superb

@Papounette79 4 жыл бұрын

Hello, is there a way to save the created files with a password? I tried adding a SaveAs Password in the code but it didn't work as expected, any help would be greatly appreciated!

@abhishekbinjola2635 5 жыл бұрын

Hi Sir, Could you please guide me that if I need to split sheet into multiple workbook but want that to freeze the top 5 rows and shows these rows in each workbook. Then what code should I use..? Please advice Sir....

@AKILALIVE4U 4 жыл бұрын

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

@ch1bob 2 жыл бұрын

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.

@abhishekthakur4054 2 жыл бұрын

Thanks sir.