Split Data into Multiple Files (Workbooks) in Excel VBA | Vivekananda Sinha | Video 156
HTML-код
- Опубликовано: 23 июн 2021
- 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
Superb one. Very useful for daily work.
Thank You Sir !
सर आपके सभी व्हिडीओ बेहतरीन होते है। बहोत कुछ नया सिखने को मिलता है ।
आपके हर नये टॉपिक का इंतजार रहता है ।
Thank you very much. Stay connected. Keep Learning 😊
It's really great session it's help me alot during reports creation
Sir
This VBA code is very useful, Further in each multiple workbooks(sheets) we required sub filter State wise in sub sheets(sheet1,..)
Thanks Sir.... Awesome
Thank you! This helped me a lot!!
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...
Amazing ❤️ Keep Learning 🚀🚀
Very useful sir tq 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
Awesome video sir. Thanks for providing such a fabulous content
Thank you so much Sanjit ji. 🙏
Lovely video
Thankyou so much
Thankyou sir g
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.
You have to add the subtotal formula inside the loop before saving the file.
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
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...
Same logic ko 2 times likhna padega for both the sheets.
agar mujhe main data sheet bhi sth le jani ho to kya code lagega
hello I need your help how do we split the data but using php
Sar aapki sikhane ki style bahut hi acchi hai
Thank you Mitesh Ji. Stay with me.
It was really helpful. Can the excel be splited in different sheet rather than a different excel, if yes then how?
Splitting into multiple sheets is super easy. I have created a tutorial on this. Please explore the videos on this channel.
Sir please creat video on merge all excel file workbook in singal workbook file
Agreed with him, please create one video on this
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
We have to write custom code for that. But will be lengthy.
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
Hi, your videos is good & informative… the same video without format changing new video posting is possible, I verified in RUclips it is not available, can you dot it?
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.
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?
@@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.
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
You have to create the table inside the loop only after copying the data code.
Sir how to select path here if my data saved in D drive's folder under Report name folder
You can use something like this D:\Report\
Very nice and helpful. Manual effort is reduced
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??
Custom program likhna parega
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
When I wrote this for my file one function couldn’t work i.e wbr. SaveAs rfolder & shT.cells (i,1) & “.xlsx”
Please check if there is any symbol or special characters are there in the name as filename doesn’t allow these symbols.
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.
Sure, you have those templates in MS Word?
@@Futureinhands No Sir, I have those templates in Excel format.
Then VBA is required
@@Futureinhands Yes Sir. Instead of creating new workbooks, I want the data to be pasted in the predefined workbooks with VBA codes.
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
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 😊
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.
There might be little difference in the program. Can you please paste your entire code here?
@@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.
Can we not create multiple worksheets files inside each file we create?
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.
Yes, we can do this. We have to right a customized code for this.
@@Futureinhands could you help us by creating a video of it or by writing the code in ur channel.
sir it says advanced filter method of range classified failed..how to fix that error?
Can you check the range used in the advance filter. Is it valid?
Sir aur ek request he ye woork book ad pdf me create hoskata he kya
PDF bhi bana sakte hai. Main bahut hi jaldi ispe bhi video banaunga.
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
I also have the same question !! Please advise !
Any idea
Can we do this split using Power Query or any other Excel feature?
Filter Function…. The new dynamic array function in Excel 2019 or later. But VBA is the most preferred method of doing this
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.
You can replace the special characters with null value as few special characters are not allowed in File name and worksheet name.
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
Can we split the same along with one common sheet (ex - Sheet 1 'common data', Sheet 2 'Department data') for all files
I do need this, please help me in this
Mere pass multiple sheet he , ek file me mujhe wahi file city wise split karna he
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
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
In that case we have to use a standard template in the VBA code
How to do it hy rows count
Why paste is not written after copying data from shtD. Range("d:d')
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
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
It can be easily done with VBA program
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
Sile ka name ka length ko dhyan rakhna padega.
Hi sir...is it possible, while split the master file into various files with pivot
Yes. Custom cose to be written.
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?
You can add the deletion code once the file is created. Something like this:
Columns("A").Delete
Columns("A:D").Delete
Sir maine kiya code but run nai hua, kya aap mere ly ek code bna skte hai. Muze excess need hai iska
Aap file download kar sakte hai description me link diya huwa hai
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
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.
Hi,
Do we have any example where we append data from a particular workbook in multiple workbooks?
Yes. I have tutorials on Data Consolidation.
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
For this customised code has to be written.
i have same issue, But i have my data with Pivot table sheet also. Dose this code will create pivot table for separated workbook ?
Yes
Sir thanks ,will this code also copy my data total active formula
And also I can my pivot table sheet will also copy
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 !!
Ising the mid, left, right function
Getting error in wbR.saveAs rFolder & shT.Cells(i,1)
Please check if there is any special characters in the name as file name doesn’t accept some special characters
Could you please let know how to create file by Employee ID wise
Same technique
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?
Workbooks.add wale code ko modify karna padega.
worksheets.add ka concept use hoga.
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
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.
Actually I had data in multiple sheet of all client and wanted to save data in multiple worksheet client wise how to do that
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
@@Futureinhands Thanks I will try this
What if I have multiple sheets in my excel and I want to separate it
There is already a tutorial on this.
@@Futureinhands can you pl provide the link
sir aap buat supreior sekha tey hey Thank you very much
Thank you very much. Stay connected. Keep Learning 😊
I have different headings in master shhet
Then we have to add a logic to match the column names and send the data column by column.
How to do new sheet format
Column width is same as original file
Using Paste Special option
I need save in binary format(. xlsb ) how to modify this code.
thks
Just change the extension in the Save As code
@@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 .
What error message you are getting?
Sir, it is showing an run time error '-2147221080':, kindly suggest how to remove this error. Error is in while loop
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.
I have tried but at the end not able to generate the files
Any error message?
No sir@@Futureinhands
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
Main iape ek video banaunga
@@FutureinhandsVery Very Good Morning sir, thank you.
Sir isme mai limited colours select Krna ho to kaise krege
Limited columns ke liye aap pura data le jane ke baad aap code se un columns ko delete kara sakte hai.
Columns("D").delete
Hi sir i have a query, how can i connect
You can ask your technical queries here
@@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
Sir, I have changed from XLSx to PDF then Opening File Showing Damanged File.
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.
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
@@Informer_Ultron Just use Save As option and select PDF as the save format.
Hi
if i want to split the data into a temple how can we do that?
Temple??
if i want to split the date into a template how can we do that
Subscript out of range error
Please download the example file from the link in the description.
Sir Sht new sheet create nhi krke remove duplicate usi sheet per kisi column maim krke ek products or bnaye split data into new file 👍🏻
How to delimit multiple excel files at once
Can you describe this in details?
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.
Plz respond
Sara record ek hi sheet me he plz sir
Maine Data consolidate ka ek tutorial create kiya hai. ruclips.net/video/7nmgcMryrxo/видео.html
Sir please creat video on merge all excel file workbook in singal workbook file
Sure