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. Select any line in the code and click on the green play button in the toolbar to run the VBA macro code.Copy the VBA code provided above and paste it in the module code window.this will open the code window for the module Hover the cursor over the Insert option.In the VB Editor, right-click on any of the objects of the workbook you’re working on.In the Code group, click on the Visual Basic option.How does the VBA code work – The above code uses a simple For Next loop that goes through each worksheet, creates a copy of the worksheet in an Excel workbook, and then saves this Excel workbook in the specified folder (which is the same that has the main file with all the sheets).īelow are the steps to place this VBA code in the Excel workbook (these will be same for all the other methods shown in this tutorial):īelow are the steps to place the code in the Visual Basic Editor where it can be executed: This is why it’s important to save the file in the folder first and then use this code. The above code is written in a way that it picks up the location of the folder using the path of the file (in which the code is run). Once you have this done, then you can put the above VBA code in the file and run the code. Save the main Excel file (which has all the worksheets that you want as separate files) in this folder.Create a folder where you want to get all the resulting files.There are a few things you need to make sure before using the above VBA code: To split these sheets into a separate Excel file, you can use the below VBA code: 'Code Created by Sumit Bansal from Ī Filename:=FPath
0 Comments
Leave a Reply. |