Now, let’s say you want to split all the sheets for 2020 and save these as separate Excel files. In case you have a lot of sheets in a workbook and you only want to split only those sheets that have a specific text in it, you can do that as well.įor example, suppose you have an Excel file where you data for multiple years and each sheet in the file has the year number as the prefix. Split Only those Worksheets that Contain a Word/Phrase into Separate Excel Files
#.ws file pdf
The above code split each sheet in the Excel file and save it as a PDF in the same folder where you have saved the main Excel file. Things you need to make sure before using this code: In case you want to split the worksheets and save these as PDF files instead of the Excel files, you can use the below code: 'Code Created by Sumit Bansal from Ī Type:=xlTypePDF, Filename:=FPath & "\" & ws.Name & ".xlsx" Split Each Worksheet and Save as a Separate PDFs This will ensure you don’t lose your data in case anything goes wrong or if Excel decides to become slow or crash. Once the code runs and split the sheets and saves these, we turn these back to TRUE.Īs a best practice, it’s recommended to create a backup copy of the main file (which has the sheets that you want to split). Note that I have used the lines Application.ScreenUpdating = False and Application.DisplayAlerts = False in the code so that everything happens in the backend and don’t see things happening on your screen. This will ensure the macro is saved and works when you open this file next. XLSM format (which is the macro-enabled format). Since you have placed a VBA code in the Excel workbook, you need to save this with a. The name of each saved file is the same as that of the sheet name it had in the main file. In case you have a lot, it may take some time.
It takes only a second if you have less number of worksheets. The above steps would instantly split the worksheets into separate Excel files and save these.
Suppose you have a workbook as shown below where you have a worksheet for each month.
Split Each Worksheet Into a Separate Excel File