Three ways to copy and paste worksheets using VBA

Copy Paste

A very common task for VBA coding and macros is to copy a worksheet, rename and update it in some way.  For example if you have an inputs and output worksheet for a given calculation that stores input assumptions and variables.  You might want to perform sensitivity analysis or stress tests on your calculations using a copy of the assumptions with only one or two variables changed.  Alternatively, you might just want the ability to store past inputs to reproduce past calculations.

In this post we are going to look at three different ways we can copy and paste a worksheet within a workbook and talk about some of the good and bad points of each.

The dot operator “.”

One of the best ways to improve your VBA coding skills is to learn your way around the Excel object model and how to use the dot operator (“.”) to navigate and select to various objects and sub-objects within it.  The best way to explain this is with a diagram and a couple of examples straight from it.  The diagram below summarises some of the main objects and methods/events within these I tend to use.

ExcelObjectModel

Now lets say we want to obtain the active worksheet in a given application.  We can do this using the dot operator as  Application.ActiveSheet .  Instead if we want to select a specific worksheet using the tab name we could do this in two ways using the dot operator:

  • Application.ThisWorkbook.Worksheets("sheetTabName")  (full qualification from the application);
  • ThisWorkbook.Worksheets("sheetTabName")  (partial qualification from ThisWorkbook);

In general I would say full qualification is better than partial qualification but I’m usually okay with using partial qualification from ThisWorkbook – not qualifying the path at all (e.g. using  Worksheets("sheetTabName") ) is generally a bad idea and can lead to really flaky code.

Hopefully the above gives you the idea of how to access different parts of the Excel object model – from the above diagram we use a “.” between each branch to get to the objects further down.  When we type this in the VB Editor we should get the intellisense popup which allows us to select the various objects and you can also use the Object browser to find your way around (and what is available) as the below picture shows.

ObjectBrowser

Copying worksheets using ActiveWorksheet

Now that we know how to navigate around the Excel object model (at least the high level principles) let’s actually use this to start copying worksheets.  The first technique we will look at uses the ActiveSheet object.  To use ActiveSheet we should be going directly from the Application (it doesn’t exist under ThisWorkbook for example) and we can copy the ActiveSheet using  Application.ActiveSheet.Copy .

The main problem with copying worksheets is that we might have trouble straight after the copying since we might not know the tab name or index or position of the new worksheet.  One way around this is to specify where the copied worksheet is placed and use this to then update the worksheet tab name.  Below is a short subroutine that shows us how we can do this.

Notice how we are using “With … End With” blocks to group our code and prevent us having to provide full qualification on every line of code.  This makes the code easier to read and cleaner overall.  The code also includes a few examples of setting certain application properties: screen updating and whether to display alerts or not.

The key downside of using the ActiveSheet object is how flaky it is … if the code is run when the wrong sheet is active then everything downstream of it breaks very quickly.  I have seen this particular problem a lot!

Copying worksheets using the tab name

A second technique which is slightly more robust than using the ActiveSheet is to use a worksheet tab name to specify which worksheet to copy.  In this case we use the general shape of  ThisWorkbook.Worksheets("tabName").Copy .  It doesn’t matter which sheet is active at a given time this will work so long as the worksheet we want to copy has the tab name set to “tabName”.  The below code is very similar to the above and performs the same thing using the worksheet tab name.

Again this technique is not perfect and the key downside of this one is that often the tab name is hard coded into a given macro and as soon as the tab name is changed by a user the code breaks.

Copying worksheets using the code name

The final technique we will cover in this post is the one I prefer to use myself wherever I can and it involves using the worksheet code name.  This is a way of accessing the object from within VBA that is not visible to the end user and which you can only change from within the VB Editor – for this reason it’s very difficult for a user to make a small change and accidentally break our code.  The below code gives an example of how to do this.

There some downsides to this also and the main one here is that you will have to specify the worksheet using the code name somewhere in your code and if you change the code name later it can mean the code no longer compiles or runs.  Personally I think this is better than having code break when a user changes a tab name!

The Exercise

For the exercise related to this post I have one last piece of background information: the formula to calculate the price of a zero coupon bond (ZCB).  Below I give a simple formula for the annually compounded ZCB price for a bond that matures in a set number of years from today.  If it’s not familiar then don’t worry about understanding why the formula is correct or really what it means (an explanation of the formula is given in Interest Rate and Bond Definitions) – the important thing is the formula itself.  We use the following notation:

  • P_k is the price of a ZCB now (i.e. the price now of a bond that pays 1 in k years);
  • k is the term to maturity in years of the ZCB;
  • Y_k is the constant, annually compounded spot rate of interest;

Then the formula for the ZCB price is given by

    \begin{displaymath} P_k = \left( \frac{1}{1+Y_k}\right)^k \end{displaymath}

For the exercise I want you to:

  1. Create a new spreadsheet and give it a sensible name.  Within this spreadsheet create a worksheet called “Inputs”;
  2. On the Inputs worksheet I want you to create input cells for:
    1. The constant annually compounded interest rate;
    2. The time to maturity of the ZCB in years;
  3. Next I want you to (manually) copy and paste the Inputs worksheet and rename it “Output_Template” (you can do this by right-clicking on the worksheet name on the tab at the bottom and selecting from the menu that pops up);
  4. On the Output_Template worksheet I want you to add an output cell for the resulting ZCB price we are going to calculate;
  5. Open the Visual Basic Editor and create a new module – give it a sensible name;
  6. Within this new module create a new subroutine (again give it a sensible name) that:
    1. Grabs the current values of interest rate and time to maturity from the Inputs worksheet;
    2. Calculates the ZCB price using the formulae above;
    3. Copies the Output_Template worksheet, pastes it to the end of the current set of worksheets;
    4. Renames this copy as “Results_Date” where “Date” should be the date and time the calculation was performed (hint: use the VBA Now() function described here);
    5. On Results_Date worksheet inserts all input parameters and the calculated ZCB price into the relevant cells;
    6. Saves the workbook;
  7. On the Inputs worksheet add a button and link this to the subroutine you just created.  Give it a sensible name;
  8. Test the button to make sure it actually works and gives the correct answer;

When working through the above you may find there are one or two small areas where I haven’t taught you every last detail of what you need to know to get to the end result (finding and re-naming the newly created worksheet using code is an example).  This is deliberate – I to develop a habit of you encountering a problem and figuring out how to solve it yourself, even if the solution is not perfect.  We start with small tasks and will progress later to more complex tasks.

If you get stuck add a comment to this post and once you’ve completed the exercise email your solution to me – I will mark and get it back with suggestions.

Examples from the post and example exercise solution

Examples from the post

Exercise example solution

Leave a Reply

Your email address will not be published. Required fields are marked *