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...
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
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?
Hi, your videos is good & informative… the same video without format changing new video posting is possible, I verified in TH-cam it is not available, can you dot it?
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.
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.
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 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.
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
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 !!
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
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 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
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?
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 😊
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 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 .
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 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.
Nice sir, Meri 3 requirement hai. 1. agar mujhe sabhi file ke last me total karna hai to kaise kare. 2. Sabhi sheet ko rename kisi perticular cell se kaise kare. 3. Mujhe splitter data ko kisi perticular raw se karna hai
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
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.
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
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
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.
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
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
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.
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...
Very informative
सर आपके सभी व्हिडीओ बेहतरीन होते है। बहोत कुछ नया सिखने को मिलता है ।
आपके हर नये टॉपिक का इंतजार रहता है ।
Thank you very much. Stay connected. Keep Learning 😊
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 🚀🚀
It's really great session it's help me alot during reports creation
Superb one. Very useful for daily work.
Thank You Sir !
Awesome video sir. Thanks for providing such a fabulous content
Thank you so much Sanjit ji. 🙏
Sir
This VBA code is very useful, Further in each multiple workbooks(sheets) we required sub filter State wise in sub sheets(sheet1,..)
Sar aapki sikhane ki style bahut hi acchi hai
Thank you Mitesh Ji. Stay with me.
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.
Thank you! This helped me a lot!!
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
Very useful sir tq 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
Lovely video
agar mujhe main data sheet bhi sth le jani ho to kya code lagega
Thanks Sir.... Awesome
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.
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.
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.
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
Thankyou so much
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
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?
hello I need your help how do we split the data but using php
Sir please creat video on merge all excel file workbook in singal workbook file
Agreed with him, please create one video on this
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.
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
Hi, your videos is good & informative… the same video without format changing new video posting is possible, I verified in TH-cam it is not available, can you dot it?
Hi,
Do we have any example where we append data from a particular workbook in multiple workbooks?
Yes. I have tutorials on Data Consolidation.
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
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.
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.
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.
Hi sir...is it possible, while split the master file into various files with pivot
Yes. Custom cose to be written.
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.
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
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
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
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
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
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.
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 😊
Can we not create multiple worksheets files inside each file we create?
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
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?
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.
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.
Nice sir,
Meri 3 requirement hai.
1. agar mujhe sabhi file ke last me total karna hai to kaise kare.
2. Sabhi sheet ko rename kisi perticular cell se kaise kare.
3. Mujhe splitter data ko kisi perticular raw se karna hai
Possible hai. Customised code likhna padega.
@@Futureinhands plz help
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
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
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.
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
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
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.
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
I have tried but at the end not able to generate the files
Any error message?
No sir@@Futureinhands
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
Very nice and helpful. Manual effort is reduced
How to do it hy rows count
Thankyou sir g
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, 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.
How to do new sheet format
Column width is same as original file
Using Paste Special option
Mere pass multiple sheet he , ek file me mujhe wahi file city wise split karna he
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
@@GaaneDeewane Just use Save As option and select PDF as the save format.
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.
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.
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
sir aap buat supreior sekha tey hey Thank you very much
Thank you very much. Stay connected. Keep Learning 😊
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
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.
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. th-cam.com/video/7nmgcMryrxo/w-d-xo.html
Subscript out of range error
Please download the example file from the link in the description.
Hi
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 👍🏻
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\
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.
Sir please creat video on merge all excel file workbook in singal workbook file
Sure