Copying Sheets

In VBA, you can copy sheets using the Copy method. For instance, to place a copy of the first worksheet at the end of the workbook, you can use a statement like this:

Worksheets(1).Copy After:=Sheets(Sheets.Count)

You can also copy sheets to different workbooks. This example copies a worksheet from Book1 and puts the copy into Book2:

Workbooks(“Book1?).Worksheets(1).Copy After:=Workbooks(“Book2?).Sheets(1)

The Copy method has two arguments, Before and After, which identify the location of the new sheet. The most you can specify is one, but you can also specify none, like in this example


By omitting both arguments, Excel will create a new workbook that contains the copied sheet. Did you ever want to send someone a spreadsheet, but didn’t want them to see everthing in it? This is a good way to isolate information into another workbook.

Posted in Uncategorized

2 thoughts on “Copying Sheets

  1. I notice that, when copying sheets in Excel 2002, I get a warning if any of the cells contains more than 255 characters. Excel tells me that the Copy Worksheet command will only copy the first 255 characters in a cell and that, if I want to copy the entire contents of such a cell, I should copy the cells instead of the worksheet.

    Does the VBA version of Copy Worksheet have a similar limitation?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.