We got a 17 000 lines ticket sales database to split in my company, your tutorial saved us DAYS of tedious work !! I don't know how to thanks you enough !!!
Works great for me, however if I have other tabs in the file can those tabs be copied including the formulas in those tabs?, also can I keep the name of the tab to stay Registered_Business_Locations_- in the new files?
Hi, Thank you for this amazing solution. But, I am facing an issue here. At 18:30 time of your video, where you run and then verify the unique count - I am getting 0 (I am expecting a number like 152). Please help!!!
I am getting an error with this text string. Any advise? I also downloaded your workfiles to test and it was the same. col.Add .Cells(RowNumber, "A").Value, CStr(.Cells(RowNumber, "A").Value) Next RowNumber
What would I write so that my output files names are saved like such as Test_CA_Version1.xlsx Where "Test" and "Version" are manual inputs into another field on a second worksheet?
One approach is to replace the special characters before you start splitting the files. Otherwise, you will have to create a function to replace the special characters in the script.
Hi, your video is really helpful! one quick question regarding the autofilter, what if I need to set up my filter on the second row of Column A, should I put ".AutoFilter .Range("A2").Column, Category_Name"?
Hello jie, thanks a lot on this. How can i add a header, use a longer names like insead of states i used company name which is more character. Thank you ❤
Hi jie, do you have any tutorial where an excel file of 15k rows can be split into multiple excels respective to row counts ( like split in batches of 600 )
hi. This is great, life saver. One question. How to I change the OUTPUT folder Like where you have Const Target_Folder As String = "C\:...." I want to insert in the excel file, let's say a new sheet and write in cell D4 the path: C:\Users\etc what to I write?
Thank you. For some reason the video is very blurred and I cannot make out the content displayed. I wonder if this my problem or it is a problem for other viewers?
Hello !!! Thank you for this tutorial. It was really helpful. I was able to split the data into different workbooks but when giving the Saveas command, I do not find the variables Save or Saveas. I only see SavedasArray. Can you help me how to define the variable Save and SaveAs ? Maybe due this I'm seeing the error Method 'Add' o object 'Workbooks' failed. Please help
Jie, Thank you for an excellent presentation of this functionality. Sharing your thought processes as you build the code is an absolutely perfect learning experience for all of us newbies! I have a question about the SavingAs message popping up as each file is saved. Is there a way to suppress these messages so that it is a smooth user experience. You put in Application. DisplayAlerts = False to achieve this but the message still appears. I also added in ScreenUpdating = False and EnableEvents = False but the message still pops up. Any help with this? Thanks again.
I tried it and it is creating each separate file but all files contain the all data. It is as if it is copy pastying all data and not only data from Filtered category value
this is a huge help. but i need to split them by Ownership Name. sometimes there are names that are longer than 31 characters. How to circumnavigate this?
Hello.... Thank you... Files are splitted into multiple files but the width of the Column is same in All files whereas in Original file, Size of the Column is different. Also filter is not applied to the splitted files whereas filter is applied in Original File. Any solution to fix it
Hey Jie, great code. I have one question though - is there a way I can base the target folder on user's input? How could I do that? Since you coded it as constant, I can not add InputBox to it...
Hi Jie, do you have any tutorial where Excel file (with multiple sheets) is split into multiple files? Example there is Sheet 1 for Costs, Sheet 2 for Sales, and I want to split data of these sheets (costs and sales) using unique code (example company codes 1, 2 & 3) - so that when it splits, the files for each company code will contain both the sheet for Costs and the sheet for Sales showing only their figures. Thanks!
Great job explaining this, Jie! Is there a way to exclude some columns from being pasted into the split files? For example, if I only want columns F:AR to be pasted into the output files.
Hello Jie, how can i retain the formulas in the file so that the equations are still there after the file split? The “paste” function does not seem to cover formulas.
Thanks for this video it worked perfectly! How can i do this if i have multiple data sets on multiple sheets that need to be split into individual files?
This tutorial is totally relevant to what I'm working right know, I've already tried the code and it's working base on what I've want, however the only error I've encountered is that if the naming of the file is exceeding my 31 characters how can I resolve this error Champ?
Hi Jie, this tutorial and VBA code is life changing. Thank you very much for that! However I would desperately need one more modification, which I have no clue how to do it. Would you be so kind to provide me guidance on how to change code if you have “header” that consists of 10 rows. My data starts at row 11, however, in each split file I would like to keep all the header data from row 1-10. Thank you!
Thank you so much for this video. I believe I'm close to completing this macro but I got an "type mismatch" error for this line " If Len(Trim(.Range("A1").Value)) > 0 Then " any advise on how to solve it? Thank you for your support
I was getting the same error. turned out there was a cell with 'vlookup' in the masterdata tab which made A1 cell of 'helper' tab a reference value, thereby throwing same error as you mentioned.
It's possible. If you want to preserve all the settings from the master file, you will need to make a copy of the original file, then filter the data on the copied workbook.
This was helpful. However i have another request and don't know how do it.. I have a file with 10k records and want to split this file with 25 records in each file but keep the headers in for each file
Hi Jie, I'm trying to split a huge file into country specific files, I have used you code which is great and does almost everything that I need. Only it copies all 25000 rows and not just the 10-2000 country specific that I need. Any suggestions?
Probably a typo somewhere along the script. If you want, you can send me your file to UA-cam@LearnDataAnalysis.org and I can take a look when I get a chance.
Just tried this for a consolidation list and it’s not bringing through anything - it’s making the files but not bringing across the data needed any help ? Sorry managed to fix this issue - is it possible to have this save into a different file path each time ? Eg by date so work I did today gets placed into today’s date and each date going forward
Hey I'm having the same issue, how did you fix it? Its created the separate files but no data is coming through, just the headers. Any help is appreciated. Thanks man
@@edschneider5533 I had to double check the VBA code for data location- in data given it’s taking it from column G I was needing column C - simple error to make - if still having issues just reply
@@undrawingend Thanks for replying, I'm using the right column to split just weird its not pulling any of the data from the table, just the headers. Wondering if it's because I'm using Microsoft 365, not sure if VBA format would be different.
hi, instead of splitting the table into separate files, could you show how to email them automatically as an attachment . for example, , every id in column A1 ( location id) will be linked to an email address on another sheet and when you start the macro, every row that contains id 1234 in location Id will separate into its own file as an attachment.
If you want to save the files as CSV vs xlsx is that possible? I tried playing with the code and changed the line. Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" To: Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".csv" but it doesn't populate the sheets until I change it back to xlsx and run that again. After it populates both the csv and xlsx files. I was hoping there was a way to make it populate the csv without having the run the program twice.
Hi Jie - Thank you for this video! Very helpful :) I hope you enjoy a coffee from me. I have one question - if I would like to split the file but only create new files for a few selected states, where within the code could I apply this change?
Thanks for the coffee, much appreciated it. Regarding your follow up question, you want to make the change to line 34 to 36. To exclude categories, you will insert an IF condition (see below): For i = 1 To collectionUniqueList.Count ' collectionUniqueList.Item(i) --> returns the category name If Not (collectionUniqueList.Item(i) = "Category Name X" Or collectionUniqueList.Item(i) = "Category Name Y") Then SplitWorksheet (collectionUniqueList.Item(i)) End If Next i To produce output files to just specific categories: For i = 1 To collectionUniqueList.Count ' collectionUniqueList.Item(i) --> returns the category name If collectionUniqueList.Item(i) = "Category Name X" Or collectionUniqueList.Item(i) = "Category Name Y" Then SplitWorksheet (collectionUniqueList.Item(i)) End If Next i
I've been trying to use this code and it looks like it is splitting correctly, but when I open the sliced files they are not filtered by the unique values in the column I selected (even though the macro generated all files with the correct names from the column I need). How can I fix that?
@@jiejenn On previous files I have saved them on a 'Very Hidden' tab and then it's looked up via a code (AA1 for example), Not sure if there is an easier way to do this?
When you say lookup a code, do you mean you already have a VBA code to extract the password and apply to an Excel file? Not sure if I follow your statement there. Regardless, you will need to figure out where VBA need to fetch the password first.
@@jiejenn Great Video! Love your work. Do you have a script which deals with the separate password or use the same password to be saved for all files created?
Hi, I have about the size of 5 Gb CSV file, can we do the same without opening the file? Because opening the file takes up a lot of time and I want to save that time and do it efficiently.
Question : In Macro file, when I split the excel and saving in .xlsx using Macro. But I want to save in Binary format .xlsb and I tried as well but it won't work for me... Pls guide me how to save binary format in Macro splits excel?
Hello. I get an error that says Run-time error '1004': Application defined or object-defined error. I copied your code exactly from the website and I even moved the column I wanted to filter to G so I didn't have to change anything except for the target folder. Do you know what could be the issue?
Maybe you can use the left function to extract just the first N characters as the file. This is the restriction set by the file system, Excel cannot override the setting.
@@beibeiilovu First, there should be an error message. Second, it is possible to debug without knowing what you typed in your code. My suggestion for you is to post your question on Stackover Flow for faster response.
@@jiejenn Hi Jei, I don't know how to get Stackover Flow. But I have a question, if the name on the column G is longer hen a tab require or contain "/" t, what can we do?
very odd... at 16: 54, when you do the debugging, in my version I get compile error for LastRow as Invalid qualifier and I can not understand why... I wrote the exact code along with you, spelling is the same.
nevermind, solved it with some Google after all :) Thank you a lot! I adapted it to a database of my own. Now the next step for me would be to build a new code which will make all aproximately 49 files protected by a certain password. I guess it is possible :)
Hi what do I need to change, wen my table does not start at 1,1 but 3,1 so A3 (header) I have got the same issue also 1004. Bug says it is: With . Range(.Cells(3, 1), .Cells(LastRow, LastColumn)) .AutoFilter .Range("D3").Column, Category_Name The second row seems to be the mistake AutoFilter...
You could also reach me on my Email or number if that would make things easier. Would be amazing if you could help, because the deadline is getting really close. Thank you very much in advance!!
I just need to keep it starting with A3 as it would not fit otherwise with the questionnaire that comes right behind the table. Cos that one needs subcategories …
Did u ever solve this problem? my table starts from the second row (A2 header) but I still want to keep the first row in the new workbooks. If I delete the first row the code runs fine but if I leave the first row in it won't work
When I run the macro I get an error Run-time error 1004 - Method 'SaveAs' of object '_Workbook' failed. Debug takes me to this line of code: wbTarget.SaveAs Target_Folder & Category_Name & ".xlsx", 51.
Hi, If I would like to save all new files in original folder, what should I put here Const Target_Folder As String = ""?I've tried to add below but doesn't workDim FPath AS StringFPath = wbTarget.PathAny suggestion on this? Thanks!
This code will returns the root directory Thisworkbook.path. If I understand your question correctly, you just need to assign Thisworkbook.path to Target_Folder variable.
@@jiejenn I wasn't able to follow. I tried putting this but it didn't work. Const Target_Folder As String = "Target_Folder variable" Apologies for being a dummy.😣
I've found a solution, although not very optimized. What happened to me was that the column I used to Filter values was outside the proviously defined Range on the wsSource sheet. This changed the expected Autofilter behaviour, and it "shifted" the column used to filter. I wanted to filter using column D but since my range actually started at column E, the autofilter shifted 4 places left and thought it should filter using column H (D+4) TL;DR from this point: I included the filter column (D, or cell "4,1") inside the range to copy successfully all contents, and then deleted the columns I didnt want to add in my final files.
Great code, but I am getting an error subscript out of range on "Set wsSource = ThisWorkbook.Worksheets("Combined")" I changed the code to match the name of my sheet and now I get this error. Thoughts? and how would I get it to ask me which column I want to use for my filtered column?
I have the same issue too. I redid this whole process 3 times with a new data set each time and I'll I type in was the word Data without spacing. I've looked up other videos too and they all have this same issue.
Thank you, helped a lot But i have one issue, Lets say its debtor aging and there is company name on top of the list. I want that company name to appear on all the excel file on top of the DATA. How to do then ?
I've tried to get this down from your video exactly as is to get it working on my own set of data with the aim of amending it then to suit my needs but i cant get this to run. No compile errors but getting "Run-time error 1004, Application-defined or object defined error" and i have no idea where the problem is. Also you dont have link to your example document. Any help is appreciated
1004 error usually indicating somewhere in your code, there is a typo somewhere. I added the finalized Excel file and source on my website you can access them @ learndataanalysis.org/how-to-split-an-excel-file-into-multiple-files-using-vba/
Thanks so much on this but my files is bit different where i need to split data by column C which is the date number, the issue is this will create issue to name the file cos the date contains "/" , hence my solution will be to create an extra column to mark the week no. and when auto- copy and paste, the macros will only copy the specific range of the columns only from Column B to the end to exclude column A where im going to use it as the name of each file. could you please help on it, 10 coffees will be given thanks!! RecordID Customer Week Beginning Week Ending Product Code Store Number Sold Qty SOH Qty SOO Qty AUPAD DRC 1/06/2020 7/06/2020 BOXGSW121D DRC_HO 2 15 AUPAD DRC 1/06/2020 7/06/2020 BOXNW1200 DRC_HO 0 0 etc...
Hey Tristan, Can you email me your two Excel files one is before and the other file will show the result after a macro is applied. My email is UA-cam@LearnDataAnalysis.org
We got a 17 000 lines ticket sales database to split in my company, your tutorial saved us DAYS of tedious work !! I don't know how to thanks you enough !!!
Glad my video helped. You can always buy me a coffee using the link in the description.
Thanks mate, i have 300,000 rows of data and ended up with 181 files, life saver.
You have saved my life! Enjoy your coffee. Best regards from Rio de Janeiro!
Thanks! And glad my video helped.
You saved me... Before using this macro, I have spent 1 month for split and merge huge excel files. Thank you so much!! Please enjoy coffee from me 😉
Cool. Glad my video helped! And thanks for the ☕.
Heyy Jie, Thanks a lot .
This is so simple to understand and saved me a lot of time .
More power to you...
Thank you, sir! This VBA was a life saver for something I needed to get done by today!
Glad my video helped. And thanks for the coffee.
Well Thank you so much for a good explanation mate.
You are a life saver. This saved me a lot of precious hours of manual work! Thank you.
you did great job
Really helpful... Thank you very much
Thank you so much for teach us this awesome script. Awesome!!!
Glad the video help.
Thank you! this was so simple to follow and saved me so much time!
Glad the video helped.
Works great for me, however if I have other tabs in the file can those tabs be copied including the formulas in those tabs?, also can I keep the name of the tab to stay Registered_Business_Locations_- in the new files?
This as a big help to me. Thank you so much!
Glad the video helped.
You are awesome you saved me :)
Hi, Thank you for this amazing solution. But, I am facing an issue here.
At 18:30 time of your video, where you run and then verify the unique count - I am getting 0 (I am expecting a number like 152).
Please help!!!
I am getting an error with this text string. Any advise? I also downloaded your workfiles to test and it was the same.
col.Add .Cells(RowNumber, "A").Value, CStr(.Cells(RowNumber, "A").Value)
Next RowNumber
me too, can anyone help us understand what's the reason?
Same here :( Any recommendation how to solve it? Thanks a lot!
Same issue. Any ideas?
Amazing-thanks!
Thank you very much Jie. Is there a way to incorporate .AutoFit into this VBA string for the output worksheets?
This is awesome!
very helpful - thank you.
nice , thanks
What would I write so that my output files names are saved like such as
Test_CA_Version1.xlsx
Where "Test" and "Version" are manual inputs into another field on a second worksheet?
Very nice tip
Thank you! The macro works perfectly until it hits a category name with a slash. Is there any way to get this to read this as 1 thing?
One approach is to replace the special characters before you start splitting the files. Otherwise, you will have to create a function to replace the special characters in the script.
Hi, your video is really helpful! one quick question regarding the autofilter, what if I need to set up my filter on the second row of Column A, should I put ".AutoFilter .Range("A2").Column, Category_Name"?
Everything works fine. However, i would want the new workbooks created to have same column width as the source sheet. How do i do that ?
That's a little more complex not something I can explain in a comment.
@@jiejenn I know. I have been trying to find a way but no help. Would using. PasteSpecial XL column width work?
Great video. How can I add some text at top of each new worksheet and start the list below that text?
I have to do this every week but with new data that I get every week. How can I use this macro to work in other excel files using the same criteria
Hello jie, thanks a lot on this.
How can i add a header, use a longer names like insead of states i used company name which is more character. Thank you ❤
Hi jie, do you have any tutorial where an excel file of 15k rows can be split into multiple excels respective to row counts ( like split in batches of 600 )
hi, I have an excel with 2 sheets, am unable to split them into separate workbooks. If you could help please
Thank you! It's really inspiring. I want to try it. Could you tell me if it's okay that the first row is only header with no content?
I don't see any problem with that.
hi. This is great, life saver. One question. How to I change the OUTPUT folder
Like where you have Const Target_Folder As String = "C\:...." I want to insert in the excel file, let's say a new sheet and write in cell D4 the path: C:\Users\etc
what to I write?
You can use the range method. For example range ("d4")
Thank you. For some reason the video is very blurred and I cannot make out the content displayed. I wonder if this my problem or it is a problem for other viewers?
Hi Jie, thanks for sharing this. Really appreciate it. How can we keep the same column width on our split files? Thanks
One approach is you can use the copy past special method to just paste the column width to set the column width to match the original tile.
@@jiejenn thank you for the prompt reply. I really appreciated and it worked.
Thank you for the tutorial... is there a way to split this by including cells with formulas?
Interesting question. Let me look into it.
Hello! What if I have a table? Seems like its not functioning if you have to maintain a table format.. :( otherwise it would be perfect... :(
Hello !!! Thank you for this tutorial. It was really helpful.
I was able to split the data into different workbooks but when giving the Saveas command, I do not find the variables Save or Saveas. I only see SavedasArray. Can you help me how to define the variable Save and SaveAs ?
Maybe due this I'm seeing the error Method 'Add' o object 'Workbooks' failed.
Please help
Jie, Thank you for an excellent presentation of this functionality. Sharing your thought processes as you build the code is an absolutely perfect learning experience for all of us newbies! I have a question about the SavingAs message popping up as each file is saved. Is there a way to suppress these messages so that it is a smooth user experience. You put in Application. DisplayAlerts = False to achieve this but the message still appears. I also added in ScreenUpdating = False and EnableEvents = False but the message still pops up. Any help with this? Thanks again.
Hum... To be honest, I can't figure out why it isn't working. I will have to look at the files to really investigate the issue.
could you please also show how to email each file to respectively email ids?
I'll look into it.
What is the category name? Is it the. States in this examples?
Dear, what if the output is required in pdf format instead of xlsx. Please let me know
You save as a PDF file.
Hello! what if i want to save the files in utf-8 csv?
You can sue the encoding parameter.
Hi, can we split a an excel file file which having two row column heading? can you help me to write the code..plz
I tried it and it is creating each separate file but all files contain the all data. It is as if it is copy pastying all data and not only data from Filtered category value
What about if my main Excel file having multiple sheets? How to modify the Macro?
this is a huge help. but i need to split them by Ownership Name. sometimes there are names that are longer than 31 characters. How to circumnavigate this?
I want to know how many references you used in this code?
This is such a lifesaver! 🙏
I was wondering if there was a way to also have the macro password protect the file based on cell in the worksheet?
Hello.... Thank you...
Files are splitted into multiple files but the width of the Column is same in All files whereas in Original file, Size of the Column is different.
Also filter is not applied to the splitted files whereas filter is applied in Original File.
Any solution to fix it
Hey Jie, great code. I have one question though - is there a way I can base the target folder on user's input? How could I do that? Since you coded it as constant, I can not add InputBox to it...
Change constant to a regular variable, then you should be able to use an inputbox.
Hi Jie, do you have any tutorial where Excel file (with multiple sheets) is split into multiple files? Example there is Sheet 1 for Costs, Sheet 2 for Sales, and I want to split data of these sheets (costs and sales) using unique code (example company codes 1, 2 & 3) - so that when it splits, the files for each company code will contain both the sheet for Costs and the sheet for Sales showing only their figures. Thanks!
I have tried this, one file only generated. Error I am getting it
Hi! I am trying this, however getting compile error as “Variable Not Defined” for SetCollectionUniqueList. Please help.
Did you declare the object?
Hey I am not able to do it in Macbook, could you please tell me how I can do it its showing 1004 Error
Hi I tried to run the macro but my excel suddenly close
This works great! But I need one optimization - how can I have it also copy over my header and footer?
Copying header and footer requiring a separate macro to handle the procedure individually.
Great job explaining this, Jie! Is there a way to exclude some columns from being pasted into the split files? For example, if I only want columns F:AR to be pasted into the output files.
One approach is to delete the unwanted columns after you export a report.
Hello Jie, how can i retain the formulas in the file so that the equations are still there after the file split? The “paste” function does not seem to cover formulas.
This is actually something I'm looking into it currently. Will give an update once I have something to show for.
Amazing.. It is so good but I can't do it
Thanks for this video it worked perfectly! How can i do this if i have multiple data sets on multiple sheets that need to be split into individual files?
Sorry about the late reply. The process is a bit more complicated if you have multiple tables on a single sheet.
No problem thanks! I figured it out creating a different script. Appreciate the help!!
@@raiderpak1 Hi...could you please let me know how you did that?
This tutorial is totally relevant to what I'm working right know, I've already tried the code and it's working base on what I've want, however the only error I've encountered is that if the naming of the file is exceeding my 31 characters how can I resolve this error Champ?
Hi Jie,
this tutorial and VBA code is life changing. Thank you very much for that! However I would desperately need one more modification, which I have no clue how to do it. Would you be so kind to provide me guidance on how to change code if you have “header” that consists of 10 rows. My data starts at row 11, however, in each split file I would like to keep all the header data from row 1-10. Thank you!
Hi Eva, giving the number of request I receive each day, I now charge $10 service fee for any support.
Hi Eva - I need the same help. My header is 13 rows. How did you solve this? Please help!!
Thank you so much for this video. I believe I'm close to completing this macro but I got an "type mismatch" error for this line " If Len(Trim(.Range("A1").Value)) > 0 Then " any advise on how to solve it? Thank you for your support
Check for typos in your script.
I was getting the same error. turned out there was a cell with 'vlookup' in the masterdata tab which made A1 cell of 'helper' tab a reference value, thereby throwing same error as you mentioned.
Hello! I am trying to keep the data validation, is it possible?
It's possible. If you want to preserve all the settings from the master file, you will need to make a copy of the original file, then filter the data on the copied workbook.
thks , here I need file save in binary format(. xlsb ) how to re_write code
This was helpful. However i have another request and don't know how do it.. I have a file with 10k records and want to split this file with 25 records in each file but keep the headers in for each file
Hi, this is excellent, but what if I have 3 worksheets, splitting only first one and copy rest worksheets to the new files ?
did you ever figure this out? trying to do the same thing
Hi Jie, I'm trying to split a huge file into country specific files, I have used you code which is great and does almost everything that I need. Only it copies all 25000 rows and not just the 10-2000 country specific that I need. Any suggestions?
Not sure if I follow. Do you know the macro is not looping through all the country? Or the macro is not copying the filtered dataset?
@@jiejenn I'm getting all the country files but its every single record, it looks like it hasn't filtered.
Probably a typo somewhere along the script. If you want, you can send me your file to UA-cam@LearnDataAnalysis.org and I can take a look when I get a chance.
Just tried this for a consolidation list and it’s not bringing through anything - it’s making the files but not bringing across the data needed any help ?
Sorry managed to fix this issue - is it possible to have this save into a different file path each time ? Eg by date so work I did today gets placed into today’s date and each date going forward
Hey I'm having the same issue, how did you fix it? Its created the separate files but no data is coming through, just the headers. Any help is appreciated. Thanks man
@@edschneider5533 I had to double check the VBA code for data location- in data given it’s taking it from column G I was needing column C - simple error to make - if still having issues just reply
@@undrawingend Thanks for replying, I'm using the right column to split just weird its not pulling any of the data from the table, just the headers. Wondering if it's because I'm using Microsoft 365, not sure if VBA format would be different.
Hi Jie Jenn ,
I was Getting a RunTime Error:1004 , While using this code !!
hi, instead of splitting the table into separate files, could you show how to email them automatically as an attachment . for example, , every id in column A1 ( location id) will be linked to an email address on another sheet and when you start the macro, every row that contains id 1234 in location Id will separate into its own file as an attachment.
That's out the scope of this tutorial, perhaps in the future.
@@jiejenn is this something you could for me privately?
@@arditiljazi6959 Send me the detail to youtube@learndataanalysis.org on what you are trying to do.
@@jiejenn I have sent you an email. Thank you
If you want to save the files as CSV vs xlsx is that possible? I tried playing with the code and changed the line.
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
To:
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".csv"
but it doesn't populate the sheets until I change it back to xlsx and run that again. After it populates both the csv and xlsx files.
I was hoping there was a way to make it populate the csv without having the run the program twice.
Hard to tell without looking at your entire code.
how to update the data file if i add new data files.?
Hi Jie - Thank you for this video! Very helpful :) I hope you enjoy a coffee from me. I have one question - if I would like to split the file but only create new files for a few selected states, where within the code could I apply this change?
Thanks for the coffee, much appreciated it. Regarding your follow up question, you want to make the change to line 34 to 36.
To exclude categories, you will insert an IF condition (see below):
For i = 1 To collectionUniqueList.Count
' collectionUniqueList.Item(i) --> returns the category name
If Not (collectionUniqueList.Item(i) = "Category Name X" Or collectionUniqueList.Item(i) = "Category Name Y") Then
SplitWorksheet (collectionUniqueList.Item(i))
End If
Next i
To produce output files to just specific categories:
For i = 1 To collectionUniqueList.Count
' collectionUniqueList.Item(i) --> returns the category name
If collectionUniqueList.Item(i) = "Category Name X" Or collectionUniqueList.Item(i) = "Category Name Y" Then
SplitWorksheet (collectionUniqueList.Item(i))
End If
Next i
@@jiejenn Perfect!! Thanks so much :)
I've been trying to use this code and it looks like it is splitting correctly, but when I open the sliced files they are not filtered by the unique values in the column I selected (even though the macro generated all files with the correct names from the column I need). How can I fix that?
Send to your file to UA-cam@LearnDataAnalysis.org and I can take a quick look.
Hi Jie, This is great. You saved me making 800 files. However, do you know if I could protect each file with a separate password for each?
It is definitely possible. I guess the first question is where are you going to store the password?
@@jiejenn On previous files I have saved them on a 'Very Hidden' tab and then it's looked up via a code (AA1 for example), Not sure if there is an easier way to do this?
When you say lookup a code, do you mean you already have a VBA code to extract the password and apply to an Excel file? Not sure if I follow your statement there. Regardless, you will need to figure out where VBA need to fetch the password first.
@@jiejenn Yeah, i already use some VBA to take a password and save/close a file using the password
@@jiejenn Great Video! Love your work. Do you have a script which deals with the separate password or use the same password to be saved for all files created?
Hi, I have about the size of 5 Gb CSV file, can we do the same without opening the file? Because opening the file takes up a lot of time and I want to save that time and do it efficiently.
did you solve it?
@Jie Jenn
I am getting the error in the line 96 what i have to for that and its running 4-5 file and what is the mean of 51 here ?
@Jie Jenn Problem solved Thank You
@@vinayakjituri9210 How did you solve it? I am also stuck in line 96
Question : In Macro file, when I split the excel and saving in .xlsx using Macro.
But I want to save in Binary format .xlsb and I tried as well but it won't work for me...
Pls guide me how to save binary format in Macro splits excel?
Hey ! I am getting 1004 error code: Autofilter method of range class failed
I will look into it.
@@jiejenn Hey I'm getting same error. .AutoFilter .Range("D1").Column, Category_Name
Hello. I get an error that says Run-time error '1004': Application defined or object-defined error. I copied your code exactly from the website and I even moved the column I wanted to filter to G so I didn't have to change anything except for the target folder. Do you know what could be the issue?
There is typo somewhere in your code. Object define error means VBA cannot find the correct reference to create an object.
My file name (which in your case is the state) exceed 23 characters, the max allowed by excel. How can I solve it?
Maybe you can use the left function to extract just the first N characters as the file. This is the restriction set by the file system, Excel cannot override the setting.
Hello, I stock in
With wsSource
With .Range(.Cells(1, 1), .Cells(LR, LC))
Can you please help to check and let me know where I put wrong?
Thank you.
Your syntax looks incorrect to me.
@@jiejenn I used LR as lastrow and LC as lastcolumn. This video did help a lot, could you please let me know to fix it? Thank you.
@@jiejenn I used your format but I stock in
With wsSource
.AutoFilterMode = False
Did I miss anything?
Thank you.
@@beibeiilovu First, there should be an error message. Second, it is possible to debug without knowing what you typed in your code. My suggestion for you is to post your question on Stackover Flow for faster response.
@@jiejenn Hi Jei, I don't know how to get Stackover Flow. But I have a question, if the name on the column G is longer hen a tab require or contain "/" t, what can we do?
very odd... at 16: 54, when you do the debugging, in my version I get compile error for LastRow as Invalid qualifier and I can not understand why... I wrote the exact code along with you, spelling is the same.
nevermind, solved it with some Google after all :) Thank you a lot! I adapted it to a database of my own. Now the next step for me would be to build a new code which will make all aproximately 49 files protected by a certain password. I guess it is possible :)
Hi what do I need to change, wen my table does not start at 1,1 but 3,1 so A3 (header)
I have got the same issue also 1004.
Bug says it is: With .
Range(.Cells(3, 1), .Cells(LastRow, LastColumn))
.AutoFilter .Range("D3").Column, Category_Name
The second row seems to be the mistake AutoFilter...
You could also reach me on my Email or number if that would make things easier. Would be amazing if you could help, because the deadline is getting really close. Thank you very much in advance!!
Also gave you an invite on LinkedIn :)
I just need to keep it starting with A3 as it would not fit otherwise with the questionnaire that comes right behind the table. Cos that one needs subcategories …
Which are placed in row 1&2
Did u ever solve this problem? my table starts from the second row (A2 header) but I still want to keep the first row in the new workbooks. If I delete the first row the code runs fine but if I leave the first row in it won't work
how to save all split files in different location? Please help:)
When I run the macro I get an error Run-time error 1004 - Method 'SaveAs' of object '_Workbook' failed. Debug takes me to this line of code: wbTarget.SaveAs Target_Folder & Category_Name & ".xlsx", 51.
Makes sure your file name doesn't contain any invalid character.
Hi Please help i am getting any error "Run time error '9' subscript out of range"
Figured it out thank you for the video it really helped me a lot.
Hi! Do you remember how you fixed it? I need help lol
Hi, If I would like to save all new files in original folder, what should I put here Const Target_Folder As String = ""?I've tried to add below but doesn't workDim FPath AS StringFPath = wbTarget.PathAny suggestion on this? Thanks!
This code will returns the root directory Thisworkbook.path. If I understand your question correctly, you just need to assign Thisworkbook.path to Target_Folder variable.
@@jiejenn Thanks!!! All your vdos are so useful. I learned a lot from this channel:)
@@jiejenn Hi there, can you teach me how to assign Thisworkbook.Path to Target_Folder...I'm lost :(
@@jiejenn I wasn't able to follow. I tried putting this but it didn't work.
Const Target_Folder As String = "Target_Folder variable"
Apologies for being a dummy.😣
Hello, can anyone help me. It's splitting the files, but no data is being returned. The headers are the only thing I see when it splits it.
I have the same problem, did you solve it?
I've found a solution, although not very optimized. What happened to me was that the column I used to Filter values was outside the proviously defined Range on the wsSource sheet.
This changed the expected Autofilter behaviour, and it "shifted" the column used to filter. I wanted to filter using column D but since my range actually started at column E, the autofilter shifted 4 places left and thought it should filter using column H (D+4)
TL;DR from this point:
I included the filter column (D, or cell "4,1") inside the range to copy successfully all contents, and then deleted the columns I didnt want to add in my final files.
Anyone can help me i have to give file name more than 31 char. how to do it??
Great code, but I am getting an error subscript out of range on "Set wsSource = ThisWorkbook.Worksheets("Combined")" I changed the code to match the name of my sheet and now I get this error. Thoughts? and how would I get it to ask me which column I want to use for my filtered column?
Makes sure your work sheet name doesn't have spaces on both ends.
@@jiejenn nope no spaces. I copied name of sheet into the code.
Then I don't know. Hard to debug without looking at your code at this point.
I have the same issue too. I redid this whole process 3 times with a new data set each time and I'll I type in was the word Data without spacing. I've looked up other videos too and they all have this same issue.
Thank you so much for this tutorial. You are an excellent instructor! Enjoy a cup of coffee on me!
Glad my video helped. And thank you for the coffee!
Nice video , i have one Large CSV file holding 12 Million rows i want to split this CSV into multiple file each file will be 1 million record
Thank you, helped a lot
But i have one issue,
Lets say its debtor aging and there is company name on top of the list.
I want that company name to appear on all the excel file on top of the DATA.
How to do then ?
I've tried to get this down from your video exactly as is to get it working on my own set of data with the aim of amending it then to suit my needs but i cant get this to run. No compile errors but getting "Run-time error 1004, Application-defined or object defined error" and i have no idea where the problem is. Also you dont have link to your example document. Any help is appreciated
1004 error usually indicating somewhere in your code, there is a typo somewhere.
I added the finalized Excel file and source on my website you can access them @ learndataanalysis.org/how-to-split-an-excel-file-into-multiple-files-using-vba/
I'm run error
Ok.
@@jiejenn Can you make more videos of the solution? wbTarget.SaveAs Target_Folder & Category_Name & ".xlsx", 51 (Error)
Thanks so much on this but my files is bit different where i need to split data by column C which is the date number, the issue is this will create issue to name the file cos the date contains "/" , hence my solution will be to create an extra column to mark the week no. and when auto- copy and paste, the macros will only copy the specific range of the columns only from Column B to the end to exclude column A where im going to use it as the name of each file. could you please help on it, 10 coffees will be given thanks!!
RecordID Customer Week Beginning Week Ending Product Code Store Number Sold Qty SOH Qty SOO Qty
AUPAD DRC 1/06/2020 7/06/2020 BOXGSW121D DRC_HO 2 15
AUPAD DRC 1/06/2020 7/06/2020 BOXNW1200 DRC_HO 0 0
etc...
Hey Tristan,
Can you email me your two Excel files one is before and the other file will show the result after a macro is applied.
My email is UA-cam@LearnDataAnalysis.org
@@jiejenn Thanks Jie, just emailed, please kindly check and let me know ! thank you for the prompt reply
Got your email + attachments. I am working on something for a client, I will give em a look once later when I am done with my stuff.
@@jiejenn Thank you so much Jie for this in advance