VBA to Split data into multiple sheets - Advance VBA Tutorial by Exceldestination
HTML-код
- Опубликовано: 23 июл 2024
- This Excel VBA Tutorial explains how to split data into multiple sheets with VBA programming.
VBA Code is explained to create multiple sheets basis on one column values and then transferring data on each sheet. We can use For Loop and If Condition in VBA with some logical use of variables and VBA Concepts to develop the macro for data split on multiple worksheets.
Buen aporte, muchas gracias. Thank you very much.
Hi nice insights ; I’ve to create a snippet to write for a table of values mxn rows and columns , how to loop in thru each table and compute six points ascending of a particular column for each row values to be compared with a constant from the table
This worked, you are the best!
thanks
Very helpful. Thank you!
You're welcome!
Simply Wonderful. Thanks Much!
welcome
This is a great video & it's very useful. Can you explain how should I name the sheet name as 1 ,2,3... based on number of different values ?
Dear Sir,When I click on submit button each & every time this button is also transfer to each sheet,& every time one blank new sheet along with other sheet is creat,why this is happening?
I know this is a video from a year ago, but I am curious as to whether I can add more headers into the code. For instance I want rows 1,2, and 3 to all be in each sheet that is created. Then my data actually begins in row 4.
Change header and starting row #'s
Code worked 💪. Thank you 😊
You're welcome!
This is a great video and I have a need for it in a construction site inspection report for multiple sites by multiple builders in a given week. All the results go into a master sheet for a given week and I want to split it out to individual building sites. Is there anyway you can put the code in the description so I don't need to type it all over? Thanks for sharing.
Hello WoodRodent, Thanks for watching! It is not possible to put the entire code in description...but I can help you...If you could send me a test email at EXCELDESTINATION@GMAIL.COM , I will respond on that with the code...
Great 💯
Sooperbbbb Explanation 👍
Thank you 🙂
Nice sir
I'm real new to VBA and really appreciate examples/tutorials that are this well done. I'm curious if you can suggest how to copy formatting from the original worksheet to worksheets that are created by the macro. Any help you can offer would be appreciated.
you can perform steps to format and record them. This recorded code you can use after data split.
@@ExcelDestination city = source_sheet.Cells(source_row, col).Value run-time error'1004'
This works fine on the data I am working on. Is there a way we can have certain or selected columns in the newly created sheets rather than having all columns? Much appreciated.
I would suggest, if you want to work on specific columns only then first paste those specific columns and then perform the task further. You can take some help from another tutorial at
ruclips.net/video/_2h2Buzhcf4/видео.html
Hi, great video. I have one question regarding new sheet where data will be inserted, instead new blank sheet can we use existing sheet which is preformatted so it would be basically like template?
Yes we can
I'm getting an error of compile error : constant expression required. I have copied your data as it is..however it is not taking my macro under that button
Const col As String= "D"
Const header_row As Integer = 1
Const starting_row As Integer = 2
This should solve the issue
The sheet names are too long and so the code stops with an error. How can I limit characters in sheet name?
Error showing on Next source_row
showing compile error: invalid next control variable reference
Thanks a lot!!!
You're welcome!
Good sir
Hi all, I keep getting Compile error, Constant expression required. Seems to be happening where i have written col.
last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row
Any help woud be greatly appreciated.
please share your code at exceldestination@gmail.com
sir, what happens after the code has filter the data ?
Does new data entered goes to the filtered destination automatically
Hi Stephen, Thanks for watching!
Great tutorial. I have written the code and tried running it, but nothing happened. Any suggestions on how to trouble shoot this? What could I be doing wrong?
You can review your code.......to check what is wrong......once you rectify your code........you would feel like expert.
What to add to the code if I only want to select 'some column values' and split the master sheet into multiple sheets?
Thank you
there are many ways of doing this. Let me share 2 ways.
First way is to delete non-required columns from master sheet and then split.
Second way is to arrange columns and then use code to create range.. For this, you can take help from following tutorial :
ruclips.net/video/6edvmcCvCkA/видео.html
@@ExcelDestination what i mean by column values is the values that belong to one column. Say a column titled 'country' which contains values like India, China, France, etc. And I want to select only China and France then split the selected column values into multiple sheets. Thank you very much for your suggestion sir.
Sir this is great code but can u set the destination file into another location like in d:/ drive how I set the path of destination file plz tell me
For any Excel VBA project, you can write to EXCELDESTINATION@GMAIL.COM.......THANKS FOR WATCHING
I know this is old but can someone please help??? I wrote the exact code in the video but am getting error and debug this line but cant make it work
last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(x1Up).Row
Thank you
You have written x1Up insted of xlUp (should be a lowercase letter L rather than a number one)
How do I select the files for consolidation Manually
Good Video, it works. where can I get common button_click()? Also, how can I set auto refresh? For lines that are changed with updated details. will this be updated on same line or new line when copied to Destination
Command button is a VBA feature. So, first you need to add developer tab in your excel file. Then you can go to Developer tab---->Insert---->Activex control and then select a command button. For auto refresh, you explore event programming with same logic.
Hi. I have an excel sheet in which data keeps on updating but the fields remain the same. How can i use this code for a generic excel sheet.
you can share your vba requirement at exceldestination@gmail.com
This is super helpful. Would it be possible for you to send me the code so I don't have to rewrite it all.
Please post the code so we can copy it
Can I get codings
This is an amazing video, easy to follow. However, what can I add on mine that when I add new data at the bottom of the master sheet it would split from there without repeating the previous data.
I have to do it monthly..
Yes you can!
@@ExcelDestination Could you share to me as well the answer to the query above?
I agree. Can you also send me the information? Mine is a directory that is forever changing. I add/remove people and would like to be able to have the "categories" split out into several sheets and have this do it automatically when a new item is added/removed. THANK YOU!
can anyone share the code please..?
This was a great video and awesome explanation, I just need to do one final adjustment.
I am going to be updating the source sheet and will run the command button multiple times. how can I set it to where it does not duplicate the info just clears the destination sheet before it imports all the data again.
I have tried the following
destination_sheet.cells.clearcontents
destination_sheet.rows.clearcontents
destination_sheet.rows("4:2000").clearcontents
I have tried these commands in different locations within the code but I I just cant figure it out. I would greatly appreciate any help.
Thank You,
This code is working well for me but I'd like an answer on this inquiry. I'd like the new data added to my source tab to be spread amongst the tabs, but not duplicated. For example, if I have 3 entries on my master list (A, B and C), I can split them perfectly. After that, if I had entries D et E and then run the macro again, entries A, B et C will also be added to the others tabs.
Because of that, entries A, B and C will be duplicated in the tabs.
Any help for that ?
Pleeeeeeease ... ;-)
Sir how can I split the data with a new workbook?
very useful video sir,
But in my file header is in 5 rows, it does not pick header with all 5 rows, it picks blank rows in destination sheet.
you can modify the code according to your data
Getting a error *application-defined or object-defined error* pls help
without looking at you code, I can not say anything. You can share your code at exceldestination@gmail.com. I will try to respond.
I have 15 sheets.
Each sheet has more than 300 filled rows and columns between 7 to 15.
Some cells value is 0 (zero) on each sheet.
I want a command button on a mastersheet.
My requirement is :-
To hide 0 value rows and columns (1 or 2 columns as per my requirement) on each sheet.
Please suggest a vba code for this.
Thanks
Thanks for your comment. You can write to exceldestination@gmail.com for any Excel VBA project.
EXCEL VBA MACRO FILE SAVE AS .XLSB format /Same sheet Name /Particular Folder
Hi,
Please help me to auto save an excel file with this below options by using VBA Macro.
1. The workbook should save as Excel Binary format (.xlsb)
2. The file name should be the same sheet name.
3. The file should save as in particular folder.
Thanks!
How to write for workbooks with random user name
Thank you sir...
Sir I m not able to add this macro to command button...
Please help me...
ok, you can watch another tutorial to see, how to add a command button and assign vba code to it. Link is :
ruclips.net/video/U4lf-GN0KSo/видео.html
Thank you sir..
Sir, after spliting data,is it possible to add data in master file & it will transfer to that particular sheet automatically...
If yes please make video on it...
Ca n you filter by the Date? using this code?
yes, we can do that.
I had to do this for my dataset, and you have to convert the date format to a date format without a / in it. Make sure everywhere he uses City to use this new date format, you can leave your data in normal format but the sheet names must not contain a / in it.
still working in 2021 thanks
welcome
@Carlos can u share the code...
can you send the excel file here? or can i have the sample of the excel file? thanks
let me check
@@ExcelDestination thank you so much. i am trying to do it but got some issues.
Excel Destination any update sir?
Superb, finally find exactly what I want, but only the problem is there, when we updating the spreadsheet and split the data, there is same datas are repeating, so please give me solution for that.
you can add some additional code to prevent duplicates while moving data to different sheet. To take idea, you can watch tutorial at below given link :
ruclips.net/video/yPUCx9HTVvo/видео.html
Can I add these codes in same button which i had working for spliting data,
@@ExcelDestination I go through this video, but I can't get idea to right the code for avoid duplication when split the data, please give the exact VBA code
can i get this code
let me check.
i got error run time error
please share the code Thanks!!
please send the email at exceldestination@gmail.com I will see if I have the original file then I will send it to you
Where is VBA code bro
FB link
Abe adotij km rkho