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.