Split Data into Multiple Files (Workbooks) in Excel VBA | Vivekananda Sinha

Split Data into Multiple Files (Workbooks) in Excel VBA | Vivekananda Sinha | Video 156

Рет қаралды 24,215

3 жыл бұрын

Split Data into Multiple Files (Workbooks) in Excel VBA
In one of my previous videos, I have shown you how to split data into multiple sheets in Excel. Based on the tutorial, I received many requests to create a tutorial on how to do the same task for multiple workbooks instead of multiple sheets. Splitting data into multiple workbooks is quite a time-taking and cumbersome because there is no direct option in Excel to do this. In this tutorial, I will show you a simple and awesome VBA code that will split data from one sheet to multiple Excel files. This will be based on a value of a specific column. Here, I have taken an example of employee data, where we are going to create department-wise Excel files based on a department column in the employee data. This will blow your mind. The code is written will work across all versions of Excel. Be it Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, or Excel 365.
★ My Excel Bundle Course ► futureinhands.com/microsoft-e...
⯆ Download the file here: futureinhands.com/youtube/ass...
★Link to Video 155: • Excel Advanced Filter ...
In this Excel tutorial we cover a few essential topics rather quickly:
How to define object variable
How to save files using VBA
How to use Advanced Filter in VBA
How to add workbook in VBA
How to add a sheet in VBA
How to use While Loop
How to split data into multiple workbooks
How to disable all alerts
How to disable screen updating
★ MUSIC IN THIS VIDEO
Music by / ikson
★ Let’s connect on social:
Our Telegram Group: fih.one/club
My LinkedIn Profile: fih.one/vs-linkedin
My Facebook Profile: fih.one/vs-facebook
Our LinkedIn Page: fih.one/linkedin
Our Facebook Page: fih.one/facebook
#FutureInHands #VivekanandaSinha #MicrosoftExcel #MicrosoftOffice #ExcelVBA

Пікірлер: 160

@ExcelMadeEasy444 3 жыл бұрын

सर आपके सभी व्‍हिडीओ बेहतरीन होते है। बहोत कुछ नया सिखने को मिलता है । आपके हर नये टॉपिक का इंतजार रहता है ।

@Futureinhands 3 жыл бұрын

Thank you very much. Stay connected. Keep Learning 😊

@prakashrawat1047 Жыл бұрын

Woow..thanks sir..❤. very useful video..keep people motivating... I spend over 1 day to found solution for this problem and this was the easiest and shortest code .. salute to you...

@Futureinhands Жыл бұрын

Amazing ❤️ Keep Learning 🚀🚀

@girishsawant8263 Жыл бұрын

Superb one. Very useful for daily work. Thank You Sir !

@LaxmanKumar-pt6ch 2 жыл бұрын

Sir This VBA code is very useful, Further in each multiple workbooks(sheets) we required sub filter State wise in sub sheets(sheet1,..)

@RaushanKumar-iw5dn Жыл бұрын

It's really great session it's help me alot during reports creation

@hsgusain2054 2 жыл бұрын

Thanks Sir.... Awesome

@Sanjit_M7 Жыл бұрын

Awesome video sir. Thanks for providing such a fabulous content

@Futureinhands Жыл бұрын

Thank you so much Sanjit ji. 🙏

@rodrigoc1980 2 жыл бұрын

Thank you! This helped me a lot!!

@vijaysahal4556 3 жыл бұрын

Very useful sir tq 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻

@Cupid_Partners Жыл бұрын

Lovely video

@rotobuck3985 7 ай бұрын

Thankyou so much

@miteshpatel7101 3 жыл бұрын

Sar aapki sikhane ki style bahut hi acchi hai

@Futureinhands 3 жыл бұрын

Thank you Mitesh Ji. Stay with me.

@mehboom7495 3 жыл бұрын

Thankyou sir g

@poonamkandpal510 Жыл бұрын

Many Thanks Sir, its really very useful. Could you please advise how can we add command for taking these separate files with subtotals in two Column.

@Futureinhands Жыл бұрын

You have to add the subtotal formula inside the loop before saving the file.

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

Mere pas 2 Sheet hai 1 workbook me, mujhe isi macro se dono sheet ko category wise 1 hi workbokk me save kerna hai..matbal har workbook me dono sheet hono chaiye...

@Futureinhands 8 ай бұрын

Same logic ko 2 times likhna padega for both the sheets.

@bhaveshoswal211 2 жыл бұрын

Sir please creat video on merge all excel file workbook in singal workbook file

@udopakhi9307 Жыл бұрын

Agreed with him, please create one video on this

@minaldiwale4821 6 ай бұрын

It was really helpful. Can the excel be splited in different sheet rather than a different excel, if yes then how?

@Futureinhands 6 ай бұрын

Splitting into multiple sheets is super easy. I have created a tutorial on this. Please explore the videos on this channel.

@guptaramdulare Жыл бұрын

want to create file with column value , and source data from multiple file each file create a new tab based on column value please suggest

@y-m26gaming39 Жыл бұрын

When I wrote this for my file one function couldn’t work i.e wbr. SaveAs rfolder & shT.cells (i,1) & “.xlsx”

@Futureinhands Жыл бұрын

Please check if there is any symbol or special characters are there in the name as filename doesn’t allow these symbols.

@flacobit 11 ай бұрын

It is workin pretty nice. Once macro have created each file, my "Happy user" is asking me to add a sort & filter (Autofilter) to each file. I do not know how to do it :S Could you send me a new macro in order to create each file, and each file add the sort & filter at each one? Thanks in advance

@Futureinhands 11 ай бұрын

The best way to get the code of sort and filter is to start the macro recording and then apply the sorting and filtering command and then stop the macro recording. Then put this code into the required place in the original code. You need to do little customization. You will enjoy the process. Thanks 😊

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

Mere pas 2 Sheet hai 1 workbook me, mujhe isi macro se dono sheet ko category wise 1 hi workbokk me save kerna hai..matbal har workbook me dono sheet hono chaiye

@kundanpathak Жыл бұрын

I also have the same question !! Please advise !

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

Any idea

@mishrabrijesh8846 Жыл бұрын

Sir how to select path here if my data saved in D drive's folder under Report name folder

@Futureinhands Жыл бұрын

You can use something like this D:\Report\

@farhanbhutto5286 Жыл бұрын

Can u please tell me if I want all the files tht I created with this vba to have one small table in the end of every sheet it makes .. urgent help sir

@Futureinhands Жыл бұрын

You have to create the table inside the loop only after copying the data code.

@kirankumar-uv7bs 2 жыл бұрын

Your youtube channel is very much knowledgeable and i have learnt lot from your channel The above Macro is very much usefull and i have used it in my workplace With referance to above video, I have on query transfer of data is done according to( column D/Department) and workbooks created , further to it in each workbook(sheets) to segregate by (CoulumnF/zone wise) ex. Human resources (workbook) (Sheet-1 North,sheet-2 centeral,sheet-3 west,sheet-4 North East) Businees development (workbook) (Sheet-1 North,sheet-2 centeral,sheet-3 west,sheet-4 North East) I sincerely seek your kind help in assisting me

@Futureinhands 2 жыл бұрын

Welcome. To create multiple sheets in the same workbook you can use advance filter code again and instead adding a code for a workbook write the code to add a sheet.

@farhanachowdhury520 Жыл бұрын

sir it says advanced filter method of range classified failed..how to fix that error?

@Futureinhands Жыл бұрын

Can you check the range used in the advance filter. Is it valid?

@Cartoons433 Ай бұрын

Input ki sheet1 me jo he split to ho jeye but baaki jo sheets he move ho ke usi split ke andar add ho gaye. Kya ese ho sakta he??

@Futureinhands Ай бұрын

Custom program likhna parega

@vinodbangalore2179 Жыл бұрын

Excellent demo. 1.instead of all columns in output how to reduce the column? 2.How to give multiple condition, like department and region again here not all but an option to select?

@Futureinhands Жыл бұрын

You can add the deletion code once the file is created. Something like this: Columns("A").Delete Columns("A:D").Delete

@rahulmandre1081 2 жыл бұрын

Dear Sir, Code sahi se work kar raha hein lekin jab workbook rename hone jate hi na toh uska naam ka character more than 30 character hein na isike liye waha error arha hein uske liye agar kuch code hein kya? Niche mene kaha error arha hein voh bhi mentioned Kiya hien plz batado shR.Name = shT.Cells(i, 1) Yaha error arha hein

@Futureinhands 2 жыл бұрын

Sile ka name ka length ko dhyan rakhna padega.

@ravishankar-cd6wj 3 ай бұрын

Sir, it cannot create a file name more than 31 character. do we can add the file name more than 31 characters. Please help on this sir

@dikishashah4350 Жыл бұрын

Hello, 1lakh data in one excel sheet. How to separate into new 10 workbook. No common data in the excel. Like 1 to 1000 one workbook, 1001 to 2000 2nd workbook is it possible. Can you please share and guide

@Futureinhands Жыл бұрын

It can be easily done with VBA program

@arubshaikh2123 2 жыл бұрын

hello I need your help how do we split the data but using php

@SomnathLondhe Жыл бұрын

Why paste is not written after copying data from shtD. Range("d:d')

@Futureinhands Жыл бұрын

Because, the this command automatically paste the data. No need to write the paste command. This is too simple: Range1.Copy Range2 The above command will paste the Range1 to Range2

@pewdiefun7218 Жыл бұрын

agar mujhe main data sheet bhi sth le jani ho to kya code lagega

@niteshit1 Жыл бұрын

Its quite useful code, need one more help in this, can we add special character in code, for example: if we have a department in one cell or multiple cells is "Business_Development" or "Marketing/Advertisement". How we will add exclusions in the same code.

@Futureinhands Жыл бұрын

You can replace the special characters with null value as few special characters are not allowed in File name and worksheet name.

@amansaurav4260 3 ай бұрын

Sir maine kiya code but run nai hua, kya aap mere ly ek code bna skte hai. Muze excess need hai iska

@Futureinhands 3 ай бұрын

Aap file download kar sakte hai description me link diya huwa hai

@kapilverma7145 Жыл бұрын

I want add additional name of workbook name e.g. File Name 1. 123-CD.xls 2. 456-CD.xls "CD" is an additional name I want to add what can I change into VBA **this "CD" value is also have in sheet but in another column

@Futureinhands Жыл бұрын

You can use the cell value in the nane. Something like this…. Just use &”-“& cells(i,5)&”.xlsx” Here it picks the value from 5th column.

@rinkirathi9757 Жыл бұрын

Very nice and helpful. Manual effort is reduced

@maheshbabus9803 Жыл бұрын

Hi, your videos is good & informative… the same video without format changing new video posting is possible, I verified in KZfaq it is not available, can you dot it?

@faizhussain1580 Жыл бұрын

Getting error in wbR.saveAs rFolder & shT.Cells(i,1)

@Futureinhands Жыл бұрын

Please check if there is any special characters in the name as file name doesn’t accept some special characters

@bathoankur Жыл бұрын

Hi, thanks for the video, please suggest how to add data for the financial year new employees in the already created files. My new file have few more new department and few more employees, original master file is lost

@Futureinhands Жыл бұрын

We have to write custom code for that. But will be lengthy.

@mehboom7495 3 жыл бұрын

Mera ek report bana he jo timesheet finger print se related he ek employe ka 30 days ka record hotata he muje same id wale employe ka ek ek kar ke alg se pdf files same formate as hedrs ke logs ke sath he plz sir help kariye plz sir

@nshailja08 2 жыл бұрын

Hi, This actually helped me a lot. Thank You. Just wanted to confirm, if I want to save these files in XLSB, is there any other code? if I change the file extension to ".xlsb" , it gives me an error. With .xlsx extension file size gets a bit heavy.

@Futureinhands 2 жыл бұрын

In the code, I have written .xlsx im the code. Have you checked by replacing it to . xlsb in the code itself and try running the code after that?

@nshailja08 2 жыл бұрын

@@Futureinhands Yes I already did that in the code it gives an error msg " This Ext can't be used with the selected file type." Actually, I wrote the code which splits master data into multiple sheets and then sends it to multiple email addresses according to the department.

@sahare33 2 жыл бұрын

Can we not create multiple worksheets files inside each file we create?

@subhomay7 2 жыл бұрын

Excellent demonstration. I have a question regarding the same procedure. What if, I have a file which shows product and product code on the same column . For example: A032, B012 ( where "A" is the product, "032" is the code). How to classify products ("A") into workbooks and then the corresponding product codes ("032") will be created in the same workbook???? Would be really glad if you could help to sort it out.

@Futureinhands 2 жыл бұрын

Yes, we can do this. We have to right a customized code for this.

@subhomay7 2 жыл бұрын

@@Futureinhands could you help us by creating a video of it or by writing the code in ur channel.

@BhaveshKalathia 8 ай бұрын

i have same issue, But i have my data with Pivot table sheet also. Dose this code will create pivot table for separated workbook ?

@Futureinhands 8 ай бұрын

Yes

@BhaveshKalathia 7 ай бұрын

Sir thanks ,will this code also copy my data total active formula

@BhaveshKalathia 7 ай бұрын

And also I can my pivot table sheet will also copy

@rajendrabhat3892 2 жыл бұрын

Actually I had data in multiple sheet of all client and wanted to save data in multiple worksheet client wise how to do that

@Futureinhands 2 жыл бұрын

In that case just start a for each loop something like this: Dim sh as worksheet For each sh in thisworkbook.worksheets Sh.copy Activeworkbook.saveas thisworkbook.path & “\” & sh.name & “.xlsx” Activeworkbook.close Next

@rajendrabhat3892 2 жыл бұрын

@@Futureinhands Thanks I will try this

@TarunKumarDelhi Жыл бұрын

Hi, Do we have any example where we append data from a particular workbook in multiple workbooks?

@Futureinhands Жыл бұрын

Yes. I have tutorials on Data Consolidation.

@sridharbadugu3819 2 жыл бұрын

Excellent Presentation thank you very much, there is standard template with logo's and Supplier name & Order Number, How we will the split data into supplier wise excel workbooks within standard template includes logo. Please share the video

@Futureinhands 2 жыл бұрын

In that case we have to use a standard template in the VBA code

@kazisazzad9257 2 жыл бұрын

Great! Thanks for sharing such an excellent video. I have some predefined invoice templates in which each supplier is different. How do I copy the data and paste those different templates? I will remain grateful to you for your suggestion.

@Futureinhands 2 жыл бұрын

Sure, you have those templates in MS Word?

@kazisazzad9257 2 жыл бұрын

@@Futureinhands No Sir, I have those templates in Excel format.

@Futureinhands 2 жыл бұрын

Then VBA is required

@kazisazzad9257 2 жыл бұрын

@@Futureinhands Yes Sir. Instead of creating new workbooks, I want the data to be pasted in the predefined workbooks with VBA codes.

@suki9860 2 жыл бұрын

Vivek, Very clear and professional presentation. Thank you! btw When I run the code, I get a Saving As message whereas I cannot see it happening in your video. Would you know the reason for this? Thanks again.

@Futureinhands 2 жыл бұрын

There might be little difference in the program. Can you please paste your entire code here?

@suki9860 2 жыл бұрын

@@Futureinhands Thanks very much for replying, Vivek. The code is exactly as per your downloaded file with absolutely nothing changed. And the code runs perfectly creating all the files correctly. It's just that the Saving As message pops up when saving each new workbook. Is it possible that there is a setting in Excel which is causing the Save As notification to be shown even though you have switched off using DisplayAlerts = False. Thank you, Vivek.

@InkaEppuduMowa 3 ай бұрын

What if I have multiple sheets in my excel and I want to separate it

@Futureinhands 3 ай бұрын

There is already a tutorial on this.

@InkaEppuduMowa 3 ай бұрын

@@Futureinhands can you pl provide the link

@girishsawant8263 Жыл бұрын

How can we separate the file name and folder name derived through "Getopenfile" method ? I want to save a new workbook in that folder after processing the data from file that we picked in that folder. For that I need to separate file name from the path... So that I can use this path to save new workbook. Thank You !!

@Futureinhands 8 ай бұрын

Ising the mid, left, right function

@BhaveshKalathia 7 ай бұрын

hello sir this work, But Now i have may source data file with Subtotal Formula & also i have source data file with Pivot table which is in other shit. So now i want make new excel file with Active total row also & also i need pivot table too in new excel source data Pls pls help me out

@vijaysahal4556 3 жыл бұрын

Very useful sir but ismein humko new workbook add nhi krke ek hi workbook main separate sheets per department ke name se data lejana ho to hum kese krenge?

@Futureinhands 3 жыл бұрын

Workbooks.add wale code ko modify karna padega. worksheets.add ka concept use hoga.

@rams0101 Жыл бұрын

Thanks for knowledge sharing. Can we split the same along with one common sheet (ex - Sheet 1 'common data', Sheet 2 'Department data') for all files

@rams0101 Жыл бұрын

Can we split the same along with one common sheet (ex - Sheet 1 'common data', Sheet 2 'Department data') for all files

@MrNaidramesh Жыл бұрын

I do need this, please help me in this

@amritasengupta4260 2 жыл бұрын

Can we do this split using Power Query or any other Excel feature?

@Futureinhands 2 жыл бұрын

Filter Function…. The new dynamic array function in Excel 2019 or later. But VBA is the most preferred method of doing this

@mehboom7495 3 жыл бұрын

Sir aur ek request he ye woork book ad pdf me create hoskata he kya

@Futureinhands 3 жыл бұрын

PDF bhi bana sakte hai. Main bahut hi jaldi ispe bhi video banaunga.

@user-uq1jj7uv4q 6 ай бұрын

I have different headings in master shhet

@Futureinhands 6 ай бұрын

Then we have to add a logic to match the column names and send the data column by column.

@vaidshreesable4593 Ай бұрын

Mere pass multiple sheet he , ek file me mujhe wahi file city wise split karna he

@nithinnvk 2 жыл бұрын

Hi sir, This was a really helpful video, but I am facing a problem, now what if I get a new file with new list, I want these also to be updated to same existing split files instead of replacing the old data.. can you please help me with this

@Futureinhands 2 жыл бұрын

For this customised code has to be written.

@rahulgajlekar6152 Жыл бұрын

Hi sir...is it possible, while split the master file into various files with pivot

@Futureinhands Жыл бұрын

Yes. Custom cose to be written.

@HarshadNadiyappara 3 ай бұрын

I have tried but at the end not able to generate the files

@Futureinhands 3 ай бұрын

Any error message?

@harshadvariya6431 3 ай бұрын

No sir@@Futureinhands

@Reza_7c 2 жыл бұрын

Could you please let know how to create file by Employee ID wise

@Futureinhands 2 жыл бұрын

Same technique

@amirsaleem2633 3 жыл бұрын

sir aap buat supreior sekha tey hey Thank you very much

@Futureinhands 3 жыл бұрын

Thank you very much. Stay connected. Keep Learning 😊

@CSSVSECENTUMWSIWB Жыл бұрын

How to do it hy rows count

@omparkashbaskandi6165 2 жыл бұрын

Sir, it is showing an run time error '-2147221080':, kindly suggest how to remove this error. Error is in while loop

@Futureinhands 2 жыл бұрын

Run time error is due to the invalid logic or calculation at that point. Please check exactly in which occurrence of the loop the error is throwing.

@Amandeep-vc6fl 3 жыл бұрын

How to do new sheet format Column width is same as original file

@Futureinhands 3 жыл бұрын

Using Paste Special option

@k.k.sabariraj6484 2 жыл бұрын

I need save in binary format(. xlsb ) how to modify this code. thks

@Futureinhands 2 жыл бұрын

Just change the extension in the Save As code

@k.k.sabariraj6484 2 жыл бұрын

@@Futureinhands hi sir, extension changed but not working sir, help me , bcz outlook mail max mb uploaded only 10mb only , so only we need to save binary format .

@Futureinhands 2 жыл бұрын

What error message you are getting?

@Keepstudying99 Жыл бұрын

Hi sir i have a query, how can i connect

@Futureinhands Жыл бұрын

You can ask your technical queries here

@Keepstudying99 Жыл бұрын

@@Futureinhands Sir I have created a macro and now has no errors but when i run the macro am not getting the output. could you please help

@sharanpujari3231 3 жыл бұрын

Sir isme mai limited colours select Krna ho to kaise krege

@Futureinhands 3 жыл бұрын

Limited columns ke liye aap pura data le jane ke baad aap code se un columns ko delete kara sakte hai. Columns("D").delete

@Informer_Ultron Жыл бұрын

Sir, I have changed from XLSx to PDF then Opening File Showing Damanged File.

@Futureinhands Жыл бұрын

It will not work. As to convert an Excel to PDF we have to goto save as and then save to PDF file. What you can do is just try to convert back to xlsx and then try to open.

@Informer_Ultron Жыл бұрын

No Sir, Excel files are Working which we getting, Just Want to Know How we Can save them as PDF... Much Needed Sir... Pls Help

@Futureinhands Жыл бұрын

@@Informer_Ultron Just use Save As option and select PDF as the save format.

@kapilbishnoi9408 Жыл бұрын

Subscript out of range error

@Futureinhands Жыл бұрын

Please download the example file from the link in the description.

@sharanpujari3231 3 жыл бұрын

Sir isme creat report alag foldar me save hota hai but reports jo creat hota hai o usi dada sheet me new sheet me kaise krege

@Futureinhands 3 жыл бұрын

Main iape ek video banaunga

@sharanpujari3231 3 жыл бұрын

@@FutureinhandsVery Very Good Morning sir, thank you.

@vaidshreesable4593 Ай бұрын

@SatishKumar-bz4ce Жыл бұрын

How to delimit multiple excel files at once

@Futureinhands Жыл бұрын

Can you describe this in details?

@SatishKumar-bz4ce Жыл бұрын

I'm working on multiple csv files where i have to open in excel and then have delimit the same for every report. Instead of doing delimit for every excel file, so required suggestion to delimit the multiple excel files at once.

@SatishKumar-bz4ce Жыл бұрын

Plz respond