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?
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
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?
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
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. 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?
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 !!!
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 !!! 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
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!!!
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 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 )
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.
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
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?
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 ❤
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?
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?
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
Dear Jie, I am big fan of your videos and you are truly life saver... however, I wanted to share the excel with you. Can you please help me out in it. I want segregartion of data City wise along with score care without removing formula. Could you please share your email please.
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!
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.
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?
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.
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 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.
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 :)
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...
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?
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.
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.
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 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.
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 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
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.
@@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?
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 RU-vid@LearnDataAnalysis.org
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.😣
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 RU-vid@LearnDataAnalysis.org and I can take a look when I get a chance.
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 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?
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?
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/
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 ?
