How to split an Excel file into multiple files using VBA

How to split an Excel file into multiple files using VBA | Excel Automation

Рет қаралды 98,125

Күн бұрын

In this tutorial, we are going to write an automation script to split a table in Excel using VBA.
Using VBA, you can break down a source worksheet to multiple Excel file based on the values in the selected key columns, and doing so, will keep your data secure without oversharing unwanted information. Many solutions out there rely on 3rd party addins, require payment to download the software. With VBA, it is free, and you can modify the script to meet your specific need.
💻 Updated Video (v2)(Formula Retained): • How To Split An Excel ...
Excel format list
docs.microsoft.com/en-us/offi...
To download the Excel file used in this video & VBA source code
learndataanalysis.org/how-to-...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood.com/jiej6
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
🗓 Get updated on new Python videos → / madeinpython
📘 More tutorial videos on my website → LearnDataAnalysis.org
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: KZfaq@LearnDataAnalysis.org
#Excel #ExcelAutomation #SplitExcelFile

Пікірлер: 196

@celiomansini 4 жыл бұрын

You have saved my life! Enjoy your coffee. Best regards from Rio de Janeiro!

@jiejenn 4 жыл бұрын

Thanks! And glad my video helped.

@edward_lee 2 жыл бұрын

Thank you, sir! This VBA was a life saver for something I needed to get done by today!

@jiejenn 2 жыл бұрын

Glad my video helped. And thanks for the coffee.

@sandrac.731 2 жыл бұрын

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 !!!

@jiejenn 2 жыл бұрын

Glad my video helped. You can always buy me a coffee using the link in the description.

@user-lu9bi4hy6b Жыл бұрын

Thanks mate, i have 300,000 rows of data and ended up with 181 files, life saver.

@yashgokhale9332 3 жыл бұрын

You are a life saver. This saved me a lot of precious hours of manual work! Thank you.

@beitsbe 2 жыл бұрын

Thank you! this was so simple to follow and saved me so much time!

@jiejenn 2 жыл бұрын

Glad the video helped.

@hanifsanatkhani4587 2 жыл бұрын

Well Thank you so much for a good explanation mate.

@balveersingh01 2 жыл бұрын

Heyy Jie, Thanks a lot . This is so simple to understand and saved me a lot of time . More power to you...

@muhammadfahadazamjamil8045 Жыл бұрын

Thank you so much for the detailed video you're the best.

@MK02599 2 жыл бұрын

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 😉

@jiejenn 2 жыл бұрын

Cool. Glad my video helped! And thanks for the ☕.

@alansandusky94 3 жыл бұрын

very helpful - thank you.

@chrisparrott5158 Жыл бұрын

This is awesome!

@yordanfy 2 жыл бұрын

Thank you so much for teach us this awesome script. Awesome!!!

@jiejenn 2 жыл бұрын

Glad the video help.

@fishfoot123 2 жыл бұрын

Really helpful... Thank you very much

@NoRickenbackers Жыл бұрын

Amazing-thanks!

@JerryMinor001 2 жыл бұрын

This as a big help to me. Thank you so much!

@jiejenn 2 жыл бұрын

Glad the video helped.

@detdet1302 2 жыл бұрын

You are awesome you saved me :)

@NavinSinghFartyal Жыл бұрын

you did great job

@nengtech6914 4 жыл бұрын

Very nice tip

@isbisb Жыл бұрын

nice , thanks

@user-st8nk1ox6g Жыл бұрын

Thank you very much Jie. Is there a way to incorporate .AutoFit into this VBA string for the output worksheets?

@weixue1113 4 ай бұрын

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"?

@rashhours 11 ай бұрын

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!

@msdyzuniga 3 жыл бұрын

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?

@albanbytyqi4973 2 жыл бұрын

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?

@Jheinsmith 16 күн бұрын

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

@sacank1 Жыл бұрын

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.

@jiejenn Жыл бұрын

One approach is to delete the unwanted columns after you export a report.

@nihadalbibani Жыл бұрын

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?

@mfarhanahussain633 7 ай бұрын

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 )

@alicemorili1330 2 жыл бұрын

Great video. How can I add some text at top of each new worksheet and start the list below that text?

@irsjadadjani489 10 ай бұрын

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?

@marcellaclara2683 2 жыл бұрын

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?

@jiejenn 2 жыл бұрын

I don't see any problem with that.

@mes1812 2 жыл бұрын

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?

@adhyayana99 Жыл бұрын

hi, I have an excel with 2 sheets, am unable to split them into separate workbooks. If you could help please

@suki9860 2 жыл бұрын

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.

@jiejenn 2 жыл бұрын

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.

@k.k.sabariraj6484 2 жыл бұрын

thks , here I need file save in binary format(. xlsb ) how to re_write code

@tejasvirao8551 8 ай бұрын

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

@rajeshramesh3636 3 жыл бұрын

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?

@user-sv3jc8vl9z 9 ай бұрын

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 ❤

@leotos5395 3 жыл бұрын

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... :(

@vanshikagupta3597 11 ай бұрын

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!!!

@Cefeusz 3 жыл бұрын

Hi, this is excellent, but what if I have 3 worksheets, splitting only first one and copy rest worksheets to the new files ?

@chriskeane6366 2 жыл бұрын

did you ever figure this out? trying to do the same thing

@Light.9 2 жыл бұрын

What about if my main Excel file having multiple sheets? How to modify the Macro?

@Zaheem4u Жыл бұрын

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

@fatimasantillan4198 3 жыл бұрын

What is the category name? Is it the. States in this examples?

@JONNEYBANSAL 11 ай бұрын

Hey I am not able to do it in Macbook, could you please tell me how I can do it its showing 1004 Error

@justinaustin5130 Жыл бұрын

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?

@jiejenn Жыл бұрын

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.

@szon9115 2 жыл бұрын

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...

@jiejenn 2 жыл бұрын

Change constant to a regular variable, then you should be able to use an inputbox.

@edmel9072 3 жыл бұрын

how to update the data file if i add new data files.?

@vishnudev1435 Жыл бұрын

Hi, can we split a an excel file file which having two row column heading? can you help me to write the code..plz

@DanielGarcia-nk8bt 2 жыл бұрын

Thank you for the tutorial... is there a way to split this by including cells with formulas?

@jiejenn 2 жыл бұрын

Interesting question. Let me look into it.

@Ohmwreckerr Жыл бұрын

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?

@somyaraiwani7938 3 жыл бұрын

I want to know how many references you used in this code?

@xiangwenqiu9330 3 жыл бұрын

how to save all split files in different location? Please help:)

@dallasxal Жыл бұрын

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

@bimal84mehta 2 жыл бұрын

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

@emilymida7755 3 жыл бұрын

This works great! But I need one optimization - how can I have it also copy over my header and footer?

@jiejenn 3 жыл бұрын

Copying header and footer requiring a separate macro to handle the procedure individually.

@nikhilnarkhede3774 3 жыл бұрын

Anyone can help me i have to give file name more than 31 char. how to do it??

@naturerelaxing3850 Жыл бұрын

Hi Jie, thanks for sharing this. Really appreciate it. How can we keep the same column width on our split files? Thanks

@jiejenn Жыл бұрын

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.

@naturerelaxing3850 Жыл бұрын

@@jiejenn thank you for the prompt reply. I really appreciated and it worked.

@dianara7658 2 жыл бұрын

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

@jiejenn 2 жыл бұрын

Check for typos in your script.

@dhankad10 Жыл бұрын

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.

@vijaysridhar9063 Жыл бұрын

I have tried this, one file only generated. Error I am getting it

@catalinarose8519 Жыл бұрын

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?

@jiejenn Жыл бұрын

You can use the range method. For example range ("d4")

@andre41184 4 жыл бұрын

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.

@andre41184 4 жыл бұрын

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 :)

@raiderpak1 3 жыл бұрын

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?

@jiejenn 3 жыл бұрын

Sorry about the late reply. The process is a bit more complicated if you have multiple tables on a single sheet.

@raiderpak1 3 жыл бұрын

No problem thanks! I figured it out creating a different script. Appreciate the help!!

@anushajayanth2388 2 жыл бұрын

@@raiderpak1 Hi...could you please let me know how you did that?

@1906931 Ай бұрын

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 ?

@jiejenn Ай бұрын

That's a little more complex not something I can explain in a comment.

@1906931 Ай бұрын

@@jiejenn I know. I have been trying to find a way but no help. Would using. PasteSpecial XL column width work?

@gohclarissa 2 жыл бұрын

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.

@jiejenn 2 жыл бұрын

This is actually something I'm looking into it currently. Will give an update once I have something to show for.

@Amy-ir6xq 3 жыл бұрын

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?

@jiejenn 3 жыл бұрын

There is typo somewhere in your code. Object define error means VBA cannot find the correct reference to create an object.

@eduardanunes9944 4 жыл бұрын

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 4 жыл бұрын

Send to your file to KZfaq@LearnDataAnalysis.org and I can take a quick look.

@ellenzpunter 4 жыл бұрын

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?

@jiejenn 4 жыл бұрын

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

@ellenzpunter 4 жыл бұрын

@@jiejenn Perfect!! Thanks so much :)

@pongepongstan8637 9 ай бұрын

Hi I tried to run the macro but my excel suddenly close

@goodmorning192 3 жыл бұрын

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.

@bmwme4689 Жыл бұрын

did you solve it?

@dumitracheflorin-daniel8605 3 ай бұрын

Hello! what if i want to save the files in utf-8 csv?

@jiejenn 3 ай бұрын

You can sue the encoding parameter.

@KeithDavid-r6t 12 күн бұрын

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.

@jiejenn 12 күн бұрын

Hard to tell without looking at your entire code.

@vinayakjituri9210 4 жыл бұрын

@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 ?

@vinayakjituri9210 4 жыл бұрын

@Jie Jenn Problem solved Thank You

@NibuKoshy 3 жыл бұрын

@@vinayakjituri9210 How did you solve it? I am also stuck in line 96

@siddharthtulsyan8405 Жыл бұрын

Hi! I am trying this, however getting compile error as “Variable Not Defined” for SetCollectionUniqueList. Please help.

@jiejenn Жыл бұрын

Did you declare the object?

@orlaghquinn3044 3 жыл бұрын

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?

@jiejenn 3 жыл бұрын

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?

@orlaghquinn3044 3 жыл бұрын

@@jiejenn I'm getting all the country files but its every single record, it looks like it hasn't filtered.

@jiejenn 3 жыл бұрын

Probably a typo somewhere along the script. If you want, you can send me your file to KZfaq@LearnDataAnalysis.org and I can take a look when I get a chance.

@evaaljancic 2 жыл бұрын

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!

@jiejenn 2 жыл бұрын

Hi Eva, giving the number of request I receive each day, I now charge $10 service fee for any support.

@vedhikha04 Жыл бұрын

Hi Eva - I need the same help. My header is 13 rows. How did you solve this? Please help!!

@tamaradissanayake5552 2 жыл бұрын

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

@chapatrux 2 жыл бұрын

me too, can anyone help us understand what's the reason?

@hoangkhai2673 Жыл бұрын

Same here :( Any recommendation how to solve it? Thanks a lot!

@ianjones9820 Жыл бұрын

Same issue. Any ideas?

@danweisberg9496 3 жыл бұрын

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.

@jiejenn 3 жыл бұрын

Makes sure your file name doesn't contain any invalid character.

@w.y.2443 3 жыл бұрын

My file name (which in your case is the state) exceed 23 characters, the max allowed by excel. How can I solve it?

@jiejenn 3 жыл бұрын

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.

@districtaccountsofficer8812 2 жыл бұрын

Dear, what if the output is required in pdf format instead of xlsx. Please let me know

@jiejenn 2 жыл бұрын

You save as a PDF file.

@jeaninescott9714 3 жыл бұрын

Thank you so much for this tutorial. You are an excellent instructor! Enjoy a cup of coffee on me!

@jiejenn 3 жыл бұрын

Glad my video helped. And thank you for the coffee!

@christinajani1672 2 жыл бұрын

Hello! I am trying to keep the data validation, is it possible?

@jiejenn 2 жыл бұрын

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.

@ChompooSuwannapruk 4 жыл бұрын

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!

@jiejenn 4 жыл бұрын

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.

@ChompooSuwannapruk 4 жыл бұрын

@@jiejenn Thanks!!! All your vdos are so useful. I learned a lot from this channel:)

@MuhammadIrfan-ng9lf 3 жыл бұрын

@@jiejenn Hi there, can you teach me how to assign Thisworkbook.Path to Target_Folder...I'm lost :(

@hassel3400 3 жыл бұрын

@@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.😣

@undrawingend 3 жыл бұрын

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

@edschneider5533 3 жыл бұрын

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

@undrawingend 3 жыл бұрын

@@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

@edschneider5533 3 жыл бұрын

@@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.

@khangnguyen-ul6ou Жыл бұрын

Amazing.. It is so good but I can't do it

@vaardanchennupati4534 4 жыл бұрын

Hi Please help i am getting any error "Run time error '9' subscript out of range"

@vaardanchennupati4534 4 жыл бұрын

Figured it out thank you for the video it really helped me a lot.

@priscillaspencer7759 Жыл бұрын

Hi! Do you remember how you fixed it? I need help lol

@harkunsith8815 2 жыл бұрын

could you please also show how to email each file to respectively email ids?

@jiejenn 2 жыл бұрын

I'll look into it.

@beibeiilovu 3 жыл бұрын

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.

@jiejenn 3 жыл бұрын

Your syntax looks incorrect to me.

@beibeiilovu 3 жыл бұрын

@@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.

@beibeiilovu 3 жыл бұрын

@@jiejenn I used your format but I stock in With wsSource .AutoFilterMode = False Did I miss anything? Thank you.

@jiejenn 3 жыл бұрын

@@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.

@beibeiilovu 3 жыл бұрын

@@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?

@arditiljazi6959 Жыл бұрын

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.

@jiejenn Жыл бұрын

That's out the scope of this tutorial, perhaps in the future.

@arditiljazi6959 Жыл бұрын

@@jiejenn is this something you could for me privately?

@jiejenn Жыл бұрын

@@arditiljazi6959 Send me the detail to youtube@learndataanalysis.org on what you are trying to do.

@arditiljazi6959 Жыл бұрын

@@jiejenn I have sent you an email. Thank you

@edschneider5533 3 жыл бұрын

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.

@TheMegaPelado 3 жыл бұрын

I have the same problem, did you solve it?

@TheMegaPelado 3 жыл бұрын

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.

@subinuresh4762 2 жыл бұрын

Hey ! I am getting 1004 error code: Autofilter method of range class failed

@jiejenn 2 жыл бұрын

I will look into it.

@nihadalbibani Жыл бұрын

@@jiejenn Hey I'm getting same error. .AutoFilter .Range("D1").Column, Category_Name

@iMadeDreamz 2 жыл бұрын

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?

@jiejenn 2 жыл бұрын

It is definitely possible. I guess the first question is where are you going to store the password?

@iMadeDreamz 2 жыл бұрын

@@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?

@jiejenn 2 жыл бұрын

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.

@iMadeDreamz 2 жыл бұрын

@@jiejenn Yeah, i already use some VBA to take a password and save/close a file using the password

@tl5769 Жыл бұрын

@@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?