How to Split Each Excel Sheet Into a Separate File

How to Split Each Excel Sheet Into a Separate File

Hello and welcome to this Excel tips video.
I am Sumit Bansal, and in this video, I’m going to show you how to split each worksheet
in a workbook into separate Excel workbooks. So here, I have this file called Example Workbook.
And here I have four sheets – Jan, Feb, Mar, and Apr . And I want to split these worksheets
and save these as individual Excel files. Now you cannot do this with any inbuilt functionality
in Excel, but you can do this easily with a simple VBA code. And before I show you the
VBA code, let me quickly tell you one thing that you need to do before using the VBA code.
You need to who have a folder and save this example workbook in that folder. And I’m doing this because I need all the
split worksheets in the same folder. So the VBA code is going to pick up the folder location
from the existing workbook and then put all the spreadsheets in this folder. Now I would go back to the workbook here,
and this is the VBA code that I’m going to use. So I’ve got to go copy this code. Now
I’m going to go to the Developer tab here and you click on Visual Basic. This is going
to open the VB editor. In case you do not have the developer tab here, you can also
use the keyboard shortcut ALT + F11 and this again opens the same VB editor.
Now, here on the left I have the project Explorer and here I have all these objects – the sheet
names and Thisworkbook. I would right-click on any of these objects, go to Insert and
click on Module. This would create a new module and here I have the code window where I’m
going to paste the code. So I will simply paste the code here. Now let me quickly take
you through the code. It’s a really simple code. What it does is
it takes the folder location from the existing workbook because the workbook is saved in
that folder already. Then it goes through each of these worksheets and it is going to
copy the worksheet and save it as an individual workbook and use the same name. So it is going
to use the same name ‘Jan’ here. Also, I’m using these two lines here, ScreenUpdating
and DisplayAlerts as False, which means that you will not see the things happening in the
backend. For you, it would be the same screen, but in the backend everything will happen.
See what happens when I run this code. Here’s a small green play button. I can click on
this button or I can use the keyboard shortcut F5. Everything is done and now if I go back to
the folder, I can see I have these four files – Jan, Feb, Mar, and Apr. If I open any of
these files, it has the same data which was there in the Jan worksheet. So this is how
you can split each sheet in an Excel workbook into individual Excel workbook. That’s it in this video. I hope you found
this useful. Also, if you’re liking these videos, please subscribe to this YouTube channel
and click on the bell icon so that you’re notified whenever I come up with the new Excel
tips video. Thank you and have a nice day.

Comments (22)

  1. Wow, thank you so much!

  2. I loved sub titles

  3. Thank you so very much

  4. Very useful trick sir, thanks for providing the VBA code it will help the user with little VBA knowledge. 👍

  5. If I want to separate the rows of same sheets…. For example if I want to separate every 100th row to next sheet or workbook …..

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

  7. Many Thanks for sharing useful tricks

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

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

  10. Hi Sumit.. handy little macro to get the job done. I love VBA.. and your tutorials which are easy to follow and always have useful tips, trick and code snippets. Thanks for sharing and Thumbs up!

  11. 👍Thanks Sumit.. another gem!

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

  13. Worked great, thank you!

  14. Will not work for me. I get a runtime error of 1004. copy method of worksheet class failed

  15. thank you sir.
    i done split each excel sheet in multiple excel file.

  16. I have the same problem as Toris. I am using Mac, and the code would not work.

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

  18. HOw can we split the data first into tab then in workbook?

  19. ws.copy gives me an error 1004: can't copy this sheet

Comment here