Join Curt Frye for an in-depth discussion in this video Copying a worksheet to a new workbook, part of Excel VBA: Managing Files and Data.
- View Offline
- Anyone who has used Excel for any length of time whether as an analyst or as a VBA programmer knows that it is absolutely vital that you create back-up copies of your data. You can do that automatically within the program and using different services many different ways. But it always makes sense to create a workbook that contains the data that you can't afford to lose. And in this movie, I will show you how to copy a worksheet to a new workbook. My sample file is the CopyToNew.xlsm sample macro-enabled workbook, and you can find it in the chapter three folder of the exercise files collection.
My goal is to copy the February worksheet which is currently shown to a new workbook. Then as an added bonus, I will also show you how to save and close the workbook you just created. So I'll press alt F11 to move over to the visual basic editor, and you should see a Subroutine called CopyToNew. The code to copy a worksheet to a new workbook is actually fairly straightforward, and it consists of two commands. The first line is Sheets followed by a left parenthesis and the name of the worksheet in double quotes.
So "February").Select. So I'm using the Sheets collection's Select method to select the February worksheet. Now press enter, and now all I need to use is the Sheets collection's Copy method. Sheets("February"), so exactly the same as the first line, .Copy. This code copies the selected sheet to a new workbook. I'll go ahead and press F5 to show you how that works.
I'll press alt tab to move to the new workbook. So alt tab, and I see a new file called Book1, and when I release the control key, I see that I have Book1, and it contains a worksheet called February. So my copy action worked. I'll go ahead and close this workbook without saving it. Close window, don't save. You can see that because I used the Copy method the original copy of the worksheet February is still in the workbook.
If I had moved the worksheet, using a separate method I'll show elsewhere, then that copy of the worksheet would be gone. Once you create your new workbook that contains the copied sheet, you can save it, and you can close it. You can either do that manually or use VBA techniques that I show you elsewhere in the course.
- Creating filters
- Chaining and combining filter criteria
- Determining whether workbooks and worksheets exist, with VBA
- Opening, closing, and saving workbooks
- Using VBA to calculate data via Excel's built-in functions
- Creating charts with sparklines
- Adding fields to UserForms