Cut up Every Excel Sheet Into Separate Information (Step-by-Step)

Watch Video – Find out how to Cut up Every Excel Sheet Into Separate Information

In case you have an Excel workbook with many worksheets, there isn’t any simple option to cut up every of those sheets into separate Excel information and save individually.

This could possibly be wanted once you sheets for various months or areas or merchandise/purchasers and also you need to rapidly get a separate workbook for every sheet (as an Excel file or as PDFs).

Whereas there’s a handbook option to cut up sheets into separate workbooks after which reserve it, it’s inefficient and error-prone.

On this tutorial, I offers you a easy VBA code that you need to use to rapidly (in a number of seconds) cut up all of the worksheets into their very own separate information after which save these in any specified folder.

Cut up Every Worksheet Right into a Separate Excel File

Suppose you have got a workbook as proven beneath the place you have got a worksheet for every month.

To separate these sheets right into a separate Excel file, you need to use the beneath VBA code:

‘Code Created by Sumit Bansal from TrumpExcel.com
Sub SplitEachWorksheet()
Dim FPath As String
FPath = Utility.ActiveWorkbook.Path
Utility.ScreenUpdating = False
Utility.DisplayAlerts = False
For Every ws In ThisWorkbook.Sheets
ws.Copy
Utility.ActiveWorkbook.SaveAs Filename:=FPath & “” & ws.Title & “.xlsx”
Utility.ActiveWorkbook.Shut False
Subsequent
Utility.DisplayAlerts = True
Utility.ScreenUpdating = True
Finish Sub

There are some things that you must ensure earlier than utilizing the above VBA code:

Create a folder the place you need to get all of the ensuing information.Save the principle Excel file (which has all of the worksheets that you really want as separate information) on this folder.

After getting this finished, then you possibly can put the above VBA code within the file and run the code.

The above code is written in a approach that it picks up the placement of the folder utilizing the trail of the file (by which the code is run). This is the reason it’s vital to save lots of the file within the folder first after which use this code.

How does the VBA code work – The above code makes use of a easy For Subsequent loop that goes by way of every worksheet, creates a duplicate of the worksheet in an Excel workbook, after which saves this Excel workbook within the specified folder (which is identical that has the principle file with all of the sheets).

Beneath are the steps to position this VBA code within the Excel workbook (these will probably be identical for all the opposite strategies proven on this tutorial):

The place to place this code?

Beneath are the steps to position the code within the Visible Fundamental Editor the place it may be executed:

The above steps would immediately cut up the worksheets into separate Excel information and save these. It takes solely a second when you’ve got much less variety of worksheets. In case you have got quite a bit, it might take a while.

The identify of every saved file is identical as that of the sheet identify it had in the principle file.

Since you have got positioned a VBA code within the Excel workbook, that you must save this with a .XLSM format (which is the macro-enabled format). This can make sure the macro is saved and works once you open this file subsequent.

Notice that I’ve used the strains Utility.ScreenUpdating = False and Utility.DisplayAlerts = False within the code in order that all the pieces occurs within the backend and don’t see issues taking place in your display screen. As soon as the code runs and cut up the sheets and saves these, we flip these again to TRUE.

As a finest follow, it’s really useful to create a backup copy of the principle file (which has the sheets that you just need to cut up). This can make sure you don’t lose your information in case something goes incorrect or if Excel decides to change into sluggish or crash.

Cut up Every Worksheet and Save as a Separate PDFs

In case you need to cut up the worksheets and save these as PDF information as an alternative of the Excel information, you need to use the beneath code:

‘Code Created by Sumit Bansal from TrumpExcel.com
Sub SplitEachWorksheet()
Dim FPath As String
FPath = Utility.ActiveWorkbook.Path
Utility.ScreenUpdating = False
Utility.DisplayAlerts = False

For Every ws In ThisWorkbook.Sheets
ws.Copy
Utility.ActiveSheet.ExportAsFixedFormat Kind:=xlTypePDF, Filename:=FPath & “” & ws.Title & “.xlsx”
Utility.ActiveWorkbook.Shut False
Subsequent

Utility.DisplayAlerts = True
Utility.ScreenUpdating = True
Finish Sub

Issues that you must ensure earlier than utilizing this code:

Create a folder the place you need to get all of the ensuing information.Save the principle Excel file (which has all of the worksheets that you really want as separate information) on this folder.

The above code cut up every sheet within the Excel file and reserve it as a PDF in the identical folder the place you have got saved the principle Excel file.

Cut up Solely these Worksheets that Comprise a Phrase/Phrase into Separate Excel Information

In case you have got a variety of sheets in a workbook and also you solely need to cut up solely these sheets which have a selected textual content in it, you are able to do that as properly.

For instance, suppose you have got an Excel file the place you information for a number of years and every sheet within the file has the yr quantity because the prefix. One thing as proven beneath:

Now, let’s say you need to cut up all of the sheets for 2020 and save these as separate Excel information. To do that, that you must one way or the other examine the identify of every worksheet and solely these sheets which have the quantity 2020 ought to be cut up and saved, and the remaining ought to be left untouched.

This may be finished utilizing the next VBA macro code:

‘Code Created by Sumit Bansal from TrumpExcel.com
Sub SplitEachWorksheet()
Dim FPath As String
Dim TexttoFind As String
TexttoFind = “2020”
FPath = Utility.ActiveWorkbook.Path
Utility.ScreenUpdating = False
Utility.DisplayAlerts = False

For Every ws In ThisWorkbook.Sheets
If InStr(1, ws.Title, TexttoFind, vbBinaryCompare) <> zero Then
ws.Copy
Utility.ActiveWorkbook.SaveAs Filename:=FPath & “” & ws.Title & “.xlsx”
Utility.ActiveWorkbook.Shut False
Finish If
Subsequent

Utility.DisplayAlerts = True
Utility.ScreenUpdating = True
Finish Sub

Within the above code, I’ve used a variable TexttoFind, which has been assigned to “2020” at first.

The VBA code then makes use of the For Subsequent loop in VBA to undergo every worksheet after which examine the identify of every worksheet INSTR operate. This operate checks whether or not the worksheet identify has the phrase 2020 in it or not. If it does, it’s going to return a place quantity the place it finds this textual content (which is 2020 on this case).

And if it doesn’t discover the textual content we’re on the lookout for, it returns zero.

That is used with the IF Then situation. So if a sheet identify has the textual content string 2020 in it, will probably be cut up and saved as a separate file. And if it doesn’t have this textual content string, the IF situation wouldn’t be met and nothing would occur.

You may additionally like the next Excel tutorials: