How to Split Each Excel Sheet Into a Separate File

In this video, I will show you how to split each worksheet in Excel into a separate Excel file/workbook.
Below is the VBA code used in the video:
'Code Created by Sumit Bansal from TrumpExcel.com
Sub SplitEachWorksheet()
Dim FPath As String
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
To use this code, you need to save the Excel workbook (from which you want to split the worksheets) in the same folder where you want the individual files.
This video is a part of my 'Excel is two-minute' series, where I share simple and quick tips to help you speed up your work and be more efficient
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: kzbin.info
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial

[@essys1991]

The information you provided was very easy to follow and time saving. Thank you very much!

[@anshu2006gupta]

nice.....It's possible to split Jan to Dec but 2 fixed worksheets with every Jan to Dec worksheets? please reply

[@Dr_Chintan_Bhuva]

Simple and amazing trick...your attitude to help others is saving so much time of everyone...may lord swaminarayan bless you with peace...

[@tds6599]

DUDE!!! YOU ARE MY NEW GOD HERO!!!!!!!!!! You literally just shaved HOURS off my work for a monthly task . . . so FREAKING COOL!!!!

[@LaNdoloChanel]

there is no developer tab on my excel, what kind of excel are you using ?

[@rishabh5041]

Do i every time paste that code in excel or it will automatically save for future

[@avishaimoscovich453]

This is great. Is there a way to modify the code to work on Excel for Mac?

[@rayanreslan4256]

So for those who are facing errors , i think i found a way to move around it , i was facing the same error , i think its because i copied the workbook and pasted it somewhere else . And maybe thats why it wasnt working. Then i created a brand new macro enabled excel in my desired location , i went to the initial one with all my sheets and data , select the first sheet , go to the very last sheet , "shift"+click on last sheet this selects the entirety of your sheets , then right click , move or copy , tick create copy , and select your brand new empty excel , you now have a new copy of your excel. Then use the macro , worked perfectly. Now , this is just my theory , it could be that moving excels around copying and cutting could interfere with the macro , or having multiple other macros also affect its functionality but i doubt that. Basically creating an excel in a folder and keeping it in that native folder without moving it around seemed to be the core for this , because the errors i was facing were path related , so maybe copying and moving excels around could f*** around with pathfinding. This worked for me hopefully it does for you aswell.

[@The_Word86]

Hi Sumit, If I wanted to take data from 1 tab and split it into individual files based on say different data in each cell So example if I have a list of groceries with prices all in one tab. Can I save individual files by category of groceries ?

[@saumitra45]

im looking for same thing, did u find it?

[@sunilpant2339]

Thank you so much....I request you that if possible ....kindly make a playlist of these utility VBA code which are required in day to day office work..... I am sure many people are looking for such playlist....

[@KaiWeissmann]

Have you found such a playlist? I would be interested in it too

[@evelynebui6540]

Hi Sumit, thanks for the tips! When I run the macro it doesn't save the files and it shows "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed. Do you know what went wrong? Thanks

[@DanHolly3]

Same for me

[@rayanreslan4256]

So for those who are facing errors , i think i found a way to move around it , i was facing the same error , i think its because i copied the workbook and pasted it somewhere else . And maybe thats why it wasnt working. Then i created a brand new macro enabled excel in my desired location , i went to the initial one with all my sheets and data , select the first sheet , go to the very last sheet , "shift"+click on last sheet this selects the entirety of your sheets , then right click , move or copy , tick create copy , and select your brand new empty excel , you now have a new copy of your excel. Then use the macro , worked perfectly. Now , this is just my theory , it could be that moving excels around copying and cutting could interfere with the macro , or having multiple other macros also affect its functionality but i doubt that. Basically creating an excel in a folder and keeping it in that native folder without moving it around seemed to be the core for this , because the errors i was facing were path related , so maybe copying and moving excels around could f*** around with pathfinding. This worked for me hopefully it does for you aswell.

[@healer2773]

Can I do this for a specific sheet only? And can I add custom path? Like it will create folder in that path and save it

[@dizzaannprieto19]

As a teacher with lacking ICT skills, this was very helpful! thank you!

[@bassplayz3568]

This looks great. However, I am experiencing a Run-Time error '1004' Method 'SaveAs' of object'_Workbook' failed - Can you help??

[@mdshaheen5983]

Please give a solution to this issues.

[@rayanreslan4256]

So for those who are facing errors , i think i found a way to move around it , i was facing the same error , i think its because i copied the workbook and pasted it somewhere else . And maybe thats why it wasnt working. Then i created a brand new macro enabled excel in my desired location , i went to the initial one with all my sheets and data , select the first sheet , go to the very last sheet , "shift"+click on last sheet this selects the entirety of your sheets , then right click , move or copy , tick create copy , and select your brand new empty excel , you now have a new copy of your excel. Then use the macro , worked perfectly. Now , this is just my theory , it could be that moving excels around copying and cutting could interfere with the macro , or having multiple other macros also affect its functionality but i doubt that. Basically creating an excel in a folder and keeping it in that native folder without moving it around seemed to be the core for this , because the errors i was facing were path related , so maybe copying and moving excels around could f*** around with pathfinding. This worked for me hopefully it does for you aswell.

[@eta8304]

is the file dynamic? does it automatically change when you change the contents of the mother file?

[@ianretallick8242]

Amazing! Thank you Sumit. Just saved me loads of time. It looks complicated but I just paused each step and then applied each action. Worked like a dream!!!

[@trumpexcel]

Glad it helped 🙂

[@GardosCreative]

Dear Sir, thank you very much for this useful video. I am having a problem on the code. When I press the debug button it highlights the line "Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" as yellow. What could be the solution. I am using a macbook by the way. Thank you for your help.

[@nagrajkhambale913]

unhide all sheets and then try

[@jentysjournals7352]

So fast and very helpful. I had to split out a very large workbook with many tabs and this did it in seconds. You are the BOMB!

[@roundfaceal9164]

Is there a way to add a phrase in addition to the name? For example I need to add 2-24 subsidy to each one

[@thangavelpradeepkarthikeya9363]

Thanks bro.. for sharing valueable info.. you have not shared the VBA code details. From where it brought.. pls make it clear.

[@aryansaluja4159]

Can we make changes to original workbook and will they reflect in splitted sheets after the split is done?

[@ceoup]

please tell me is it possible to split single sheet in multiple workbooks (on the basis of there is 75 districts in one column want different workbooks for each District )

[@trumpexcel]

It can be done with VBA. You will have to create the code for it

[@ketanshah2855]

Hi Sumit - nice simple code. How do I copy with formula replaced by values ?

[@niharranjan7206]

Hi Sumit. Thank you for the VBA code give for split each excel sheet into a separate file. After added the VBA code, it is working fine.But now when I open any excel all the sheet which were there in original file are showing in VBA project and opening with any excel opened. Help me to resolve the same.

[@meleuangelion7675]

Wow! This was simple and straightforward 👏 TY so much for making life “at work” so much simpler 🥰

[@sumitbarman5895]

Just an observation: Unhide all the sheets before debugging the code or it will through error. Thanks for the video sir.

[@learnwithme24-7]

Thank you. It was a great tip

[@xeniaorozco5557]

YOU SAVED MY LIFE! I was trying to run the module for the past hour and it was giving me an error and I COULD NOT figure out what it was. I had one sheet hidden!! thank you!!!

[@LayanMalhamrani]

I don't have any sheets hiddin and it still showed me a bug in "Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx""

[@manigandanrajagopal5238]

Hi Very nice video. Easy understand and in short time. I tried same code with csv extension. it is worked but my data team rejected as corrupted file unable import to server

[@manigandanrajagopal5238]

Even when i se the file data in preview it is showing corrupted condition only

[@KeithHarrison73]

This was a life saver. I needed to create 95 individual files from the 95 sheets in my workbook. It was fast and easy using your vba script! Each file had to follow a specific naming convention, but I was able to tweak your script to get exactly what I needed. Thank you.

[@pearl4177]

Do you know how to share these files then via mail??

[@KeithHarrison73]

@@pearl4177 - Are you looking for an added step- automatically emailing each file to a specific recipient? I’m sure that can be done, but I don’t know how to do it. I only needed to upload all of my individual files to a specific drive.

[@KeithHarrison73]

@@pearl4177 - Try this video to see if it’s what you need: kzbin.info/www/bejne/Z4qwgZ-qn96Lm6M

[@pearl4177]

@@KeithHarrison73 thank you Keith for the link..

[@KeithDavid-r6t]

I loved the video but had to make a new sheet vs copy my old data file into the folder to avoid getting an error. 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.

[@vinodsiwan123]

i am getting error path/file access error: :\VB8F8E.tmp' when code go to ws.copy every time i running the vba code this is find through step into (F8). Please help as three times this code executed but after it is showing same error . How we overcome this hurdle.

[@rayanreslan4256]

So for those who are facing errors , i think i found a way to move around it , i was facing the same error , i think its because i copied the workbook and pasted it somewhere else . And maybe thats why it wasnt working. Then i created a brand new macro enabled excel in my desired location , i went to the initial one with all my sheets and data , select the first sheet , go to the very last sheet , "shift"+click on last sheet this selects the entirety of your sheets , then right click , move or copy , tick create copy , and select your brand new empty excel , you now have a new copy of your excel. Then use the macro , worked perfectly. Now , this is just my theory , it could be that moving excels around copying and cutting could interfere with the macro , or having multiple other macros also affect its functionality but i doubt that. Basically creating an excel in a folder and keeping it in that native folder without moving it around seemed to be the core for this , because the errors i was facing were path related , so maybe copying and moving excels around could f*** around with pathfinding. This worked for me hopefully it does for you aswell.

[@giancarloforero7684]

very good video. Let's say i needed to filter out information from those excel sheets into their own sheets (ex: one sheet has date mon-fri. I want an entire sheet of Monday's information on its own sheet but in the same excel file as fri). How could I do that?

[@SBScott]

Worked like a charm. Easy to follow instructions and the explanation of why you did it the way you did was helpful.

[@pravallikabhanu4847]

Hello, thanks for the video! a small doubt please, actually i need to copy the data validation link sheet also into the msplittled files, please help

[@thisistamal]

Sir, not able to use this code once I am saving this in module under personal macro workbook and apply for other open workbook. Any solution to this?

[@Lo_Loan_Loanne]

Hi Sumit, thank you for the great video, much appreciated. Just wondering if we can do 1 step further in this VBA code and only copy as values and number formats?

[@abhishekdas2640]

If I change the extension in your code ".xlsx" to ".csv" will the files save as CSV files. please let me know...please

[@naisargishah7900]

Idk how to thank enough... You saved my precious time 🥺

[@AlexBruce-t3m]

Hi @trumpexcel I keep getting the following error - compile error: expected end of statement. What can I do to resolve this?

[@weiyuquan2425]

Hi is there a way to do this without VBA? Our IT security has disabled VBA so we cannot use it.

[@aashraynalawade]

Hi Sumit, I have similar Query. I have 2 sheets in one Workbook. With one unique column. Basis this Column I want to spilt this Workbook where both sheets should be filtered n save into new sheets basis value /filter I. This unique column. Is that possible?

[@sahil23802]

I'm looking for something similar. DId you find it?

[@brajamohansahoo99]

Very nice & simple code sir. Please make a video for consolidate multiple workbooks having multiple sheets. Consolidated workbook should have multiple worksheets also

[@cristinamiranda2310]

Thanks I become expert in a split seconds!

[@albieschoeman4006]

Thanks Man, Saved my days of work

[@muhammadalsadah1219]

Hi 👋, how to do the same for 100s of workbooks in a folder? That is, I want to have the code in a seperate file and when executed it does this for each workbook within the directory.

[@shanijaiswal2296]

Amazing video. Actually, it was an imagine till now but today it has come true only because of you. Thanks a lot.

[@SAJIDALi-rj6go]

This is really easy to follow!!! Thank you so much to make my life easier!!!!

[@mynameisricon1]

YOU SAVED MY LIFE, LITERALLY

[@SueStopka]

This was so great and I'm not a techie at all. Thanks so much!

[@moesadr3342]

How about if we need to save them all sheets in .csv format as well?

[@yahyamahmoud9183]

How to do the opposite.? I mean if you have many documents and you want to make them in one documents (many sheets)?

[@danishdani21]

thank you for helping but problem is that if some workbook is hide this VB is not working, please help

[@sahil23802]

HI Is there a possibility that when i have 2 sheets, 1 sheet is cut like explained in video and other sheet automatically gets copied in all.

[@torisstephens4438]

Tried to run the code but it doesn't work on with Mac, getting errors. Can you provide the update?

[@dawnofvoice]

I'm getting an error when I do this on my Mac: "Run-time error '104': Method 'SaveAs' of object '_Workbook' failed. I copied and pasted the formula from here so I know there are no typos. Help please!

[@jerrycampisi]

help. this isnt working? it says theres an error with the part of the code "ws. copy" but i dont know what that means. thank you

[@naisargishah7900]

Hey how do I save the same in different file format? I want to save as Excel 97-2003 workbook

[@color1968]

This is fantastic, save lot of my time. Big thank is not enough to show my appreciation.

[@trumpexcel]

Glad the video helped 😀

[@WantPeaceForAll]

Thanks for making this so fast & easy! What if we want to only pull out ONE sheet to make that a separate file (not all the sheets into separate files)? Is that possible? Please let me know. Thanks!

[@KalpanamajhiKalpana-we5lk]

First time it's work. When I did again on other file it's not working. This code is creating a blank file with name of Sheet1. Please suggest how can I solve this?

[@joshimudit]

Rather than saving what if we need to mail each split sheet to respective mail id then what is to be done?

[@atharvajogdeo1225]

I have nothing to say man ..🙏🙏🙏 U r video is ssrly working as a trump card for me...thank u so much

[@szargarepa]

Many thanks for this video and sharing the code! I managed to adjust it a bit for my needs and you just saved me and a friend of mine a couple of hours every week :D Cheers!

[@wahyuhanifairusshaba]

thank you for your code!! i did my job done in a second!

[@tigranyeremyan7634]

I am trying to save the tabs to a CSV format. However when i update the code from '.xlsx' to '.csv'. The macro runs fine, but when it comes time to open the new csv file, i get a warning message "the file format and extension do not match" This is a problem because now I can not upload the new file to an online database I have. Any suggestions?

[@kuldeepmanral-i1y]

What is the solution, this VBA code is not working in my Excel file, when I copy paste in the Excel.

[@mechimansilla]

can this be done with numbers?

[@w.y.2443]

Hi, I have an issue that the name of the file contains more characters than allowed. How to solve it?

[@kevlake2005]

Same issue. Character limit is 20

[@suryaboda2800]

I don't have developer option in my Excel. After Data its Review , View, Help that's it . What can be done in this case ? Please help.

[@rccarissue1562]

I get an error in the code at the ws.copy line. Any help would be appreciated. It is a run time 1004 error. "Copy method of worksheet class failed"

[@098silvio]

Hello, can you help me please. I need to Export 7 foro 20 worksheets as a separate files, not as one.

[@jonnathanjon1]

Hello..I saved the macro as a add in .So when it try to save the file its saving to the folder where Personal.XLSB folder . How can I save it to the same active workbook folder ? appreciate your help

[@robertadafallon660]

Took me less than 5 minutes!! Thank you sir!!!

[@trumpexcel]

Glad the video worked for you 🙂

[@leeblignaut2826]

Hello Sumit, how do I run this same vba code but I require the additional workbooks to be hard coded when saved? I have a model with >200 worksheets which I need extracted, but when I run your vba code, these workbooks still contain formulas and will only work if my main workbook is open. Please advise? Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

[@charlesdawnbeltran9603]

Nice video. Is is possible to split Jan and Feb (or 2 sheets) into a single file?

[@cherechukwuezeh3112]

Thanks a lot for this, it just saved me 2hrs work

[@ndrmahmoudi]

TrumpExcel I tried this code, but it gives me an error. It says "copy method of worksheet fails". How can I fix this?

[@rajkumarreddy1620]

This is not a simple help at free of cost. Really very helpful. Thank you.

[@anower120]

Thank you for making my life easier! It is very helpful for me.

[@herryvaliant7689]

can you make another macro that let the user choose which sheet to separate and another ones to let attached to original workbook?

[@HelenaYing_creates]

thanks for sharing. just tried the code it works. tips for Mac users, need to change "\" to "/"

[@LayanMalhamrani]

it still showed me a bug in "Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx""

[@nelukanuwan]

superb. it's really helpfull thank you soo much.

[@shivangiagrawal2665]

This worked for me so well. Thank you

[@rajahuja270]

Hi Summit. I learned so many tricks of Excel from your videos that in my office I have become an Excel Wiz. Thanks to you. Regarding the VBA code you showed in your video, where can we copy or download it? Please advise.

[@stevenmccauley254]

Look below the video title & click more. Under that you'll see the code to copy & paste. It took me a few times of watching the videos to understand the process. I've now use it a few times & it works perfectly. What a great video!

[@VikasKumar-hq9rl]

So nice, But how to change path ? Pls respond

[@AfrojMedia]

what if i need only selected sheet ? can you please guide?

[@amanullahhaniffa1511]

Is there a way to create multiple powerpoint slides from a single page of word documents

[@thankgodconquest3877]

This is a life saver. Thank you so much

[@MrKagiMoni]

GOLD. Pure GOLD. Thanks Bansal :)

[@elliea4492]

where did you find the VBA code? or did you create it?

[@trumpexcel]

Wrote the code... I don't remember, but I am sure I must have referred to some amazing forums or websites to get help in writing it

[@cristovaorocha3516]

This is awesome, works like a charm! Thank you, Sir!

[@trumpexcel]

Glad you found the video useful :)

[@fruity_cereal_IDV]

OMG TYSM SAVED MY LIFE FR

[@trumpexcel]

Glad you found the video helpful!

[@MAB_FinTech]

Awesome Tip - This is very efficient! I hate manual work and creating 100s of these files each quarter. Although I review these and it's done by my colleague but even reviewing each of the files one by one is a Pain! This is great, Thank you very much for this tip. You earned yourself a Sub! :)

[@shameerhans4967]

there is any cade to bring all other sheet in to single workbook

[@Roy_trades]

I edited the code from excel to pdf and it didn't work the pdfs can't open please help me on how i can do this

[@chinthagurunathreddy]

Thank you it saved me 30 mins

[@stevemarshall8873]

You're a legend! Thank you sir!

[@nikolayvoznyak84]

Thank you! Can we do the opposite using VBA: combine a few workbooks into one? Please advise.

[@comedychannel5854]

i want to give the user option to select the file before running this macro. Any idea?

[@andrewsegawa7221]

Thanks for this tutorial. However, my code created just one workbook and threw this error: "Run-time Error '1004: Exception has been thrown by the target invocation.'

[@kingslyamalanathan983]

wow.. it is awesome. thanks 1000 times you saved me! I was struggling to do this, but with your video, it has become very easy and simple .. thanks ..thanks..thanks.