Hello and welcome back to the Excel Tip of the Week. This week, we have a Developer level post in which we are going to work through the VBA code required to take a workbook and cut it into one file for each sheet, and save all the resulting files.
Concept one - active workbooks and worksheets
Excel at any time has an active workbook and active worksheet. This is typically the currently displayed workbook / sheet. You can operate on these using ActiveWorkbook.[action] and ActiveSheet.[action].
You can store the current workbook / worksheet in a variable with Set BLAH = ActiveWorkbook / ActiveSheet.
You can change the active item to a stored value with BLAH.Activate.
Concept two - Save As
You can run the "Save As" dialogue from VBA. There are a lot of extra variables for e.g. setting passwords and the like, but the basic method is:
If no folder path is specified, the default is to use the current location. If you're running a macro within the workbook you're working on, that might be fine, but if your macro is stored in Personal.xlsb or an Add-In, that's going to be no good. To fix that, you will need to specify the file path where you want the file saved as well.
We are going to create a loop that will take each worksheet in turn, copy it to a new workbook, save the new workbook (using the sheet name as the filename), and then move to the next sheet. This version leaves the new files open but you could close them if you wanted to.
Because our code is designed to sit in Personal.xlsb and be used with a keyboard shortcut, we need to make sure that the new files are saved in the folder where the original file is. We also aren't going to delete the original file as a precaution in case anything goes wrong (e.g. there is already a file in the folder with the same name as one of the new ones we are trying to create).
Here's our full code:
Sub Splitter()Dim i As IntegerDim FPath As StringDim ParentFile As Workbook
FPath = ActiveWorkbook.PathSet ParentFile = ActiveWorkbook
For i = 1 To ActiveWorkbook.Sheets.Count ParentFile.Activate ActiveWorkbook.Sheets(i).Copy ActiveWorkbook.SaveAs (FPath & Application.PathSeparator & ActiveSheet.Name)Next i
Just a couple of explanatory points:
And that's it!
Previous post - Revisiting working with multiple screensTOTW index Next post - Revisiting SUMPRODUCT
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
It's worth noting that the following code:
can be reduced to
VBA is always more efficient and quicker when you don't activate and select unnecessarily