I recently worked on a project where my client had a Master template-sheet that needed to be duplicated and populated repeatedly. I needed to duplicate a sheet, and retain a reference to it, so I can rename it, reposition it, and pass it to another sub that populates it with data. I assumed there must be a very straight forward way to that, but was utterly disappointed when I found out there wasn’t.

Excel lets you create a sheet and maintain a reference to the created worksheet object easily:

Dim MySheet As Worksheet
Set MySheet = ThisWorkbook.Worksheets.Add

One would naturally expect you could do that with the Copy method, but I am afraid Excel does not work that way. The following code does not let you duplicate a sheet and retain a reference to it:

Dim ToCopy As Worksheet, _
	CopiedSheet As Worksheet

Set ToCopy = ThisWorkbook.Worksheets("SomeSheet")
Set CopiedSheet = ToCopy.Copy(After:=ToCopy)

Some facts about the Copy method of the worksheet and chart objects

I did some research and found some useful and bizarre facts:

1. The copied sheet becomes the active sheet.
   • You can get a reference to the duplicated sheet by using the ActiveSheet method.
   • However, the sheet that is being duplicated has to be visible for this method to work.
2. If you make a copy of a sheet, and specify the After property of the copy method to a particular sheet, Excel places the duplicate sheet after all the invisible sheets, which immediately follow the specified sheet, if there be any.
   • You cannot simply copy a sheet after a specific sheet, and use the index to get a reference to it, if there are invisible sheets in your WorkBook

Before we dive into the subject, I recommend reading the following article I wrote to understand the distinction between the Sheets and the Worksheets collection: When do I use the Sheets Collection in Excel VBA?

In our case, the order in which the sheets are positioned is important and since you may have Chart sheets in your workbooks, it makes more sense to use the Index property of the Sheets collection, rather than the Worksheets collection. Note that, in the functions that I am suggesting below, I am declaring the arguments as Object instead of Worksheet for the same reason.

Two Ways to Duplicate a Sheet and retain a Reference to it

Considering the facts above, here are two methods that one can use to copy a sheet and retain a reference to it.

Active Sheet Method

This method remembers the visible state of the sheet that is being copied, in-hides it, makes a copy, retains a reference to the active sheet, and finally restores the visibility state of the sheet that was copied.

Function DuplicateSheetActivate(WhichSheet As Object) As Object

Dim CopySheetVisible As XlSheetVisibility

'Remember the visibility state of the sheet that is being copied
CopySheetVisible = WhichSheet.Visible
'Make the sheet that is being copied, visible
WhichSheet.Visible = xlSheetVisible
'Make a copy of the sheet
WhichSheet.Copy after:=WhichSheet
'Since the sheet was made visible already, the duplicated
'sheet will definitely be the active sheet.
'Use the ActiveSheet object to get a reference to the
'duplicated sheet
Set DuplicateSheetActivate = Application.ActiveSheet
'Set a temporary name to the duplicated sheet
DuplicateSheetActivate.Name = WhichSheet.Name & " Duplicate"
'Restore the visibility state
WhichSheet.Visible = CopySheetVisible

End Function

Index Method

If for some reason, you feel it is a shame to rely on the ActiveSheet property, like me, and want to rely on logic and logic alone, perhaps you can try this function on for size. This method uses the Worksheet Object’s Index property to obtain a reference to the copied sheet.

This method makes the sheet that is being copied, and the sheet that immediately succeeds it, visible first. Then it makes the copy, and retains a reference. Finally, it restores the visibility state of the sheets. The previous method does not guarantee that the duplicated sheet will be placed right after the copied sheet, while this does.

Function DuplicateSheet(WhichSheet As Object) As Object

Dim CopySheetVisible As XlSheetVisibility, _
    NextSheetVisible As XlSheetVisibility, _
    NextSheet As Object

'Remember the visibility state of the sheet that is being copied
CopySheetVisible = WhichSheet.Visible
'Make the sheet that is being copied, visible
WhichSheet.Visible = xlSheetVisible
'If the sheet that is being copied is not the last sheet, then
'get the next sheet, and remember it's visibility state
If Sheets(WhichSheet.Name).Index < ThisWorkbook.Sheets.Count Then
    Set NextSheet = ThisWorkbook.Sheets(Sheets(WhichSheet.Name) _
        .Index + 1)
    NextSheetVisible = NextSheet.Visible
    NextSheet.Visible = xlSheetVisible
End If

'Make a copy of the sheet
WhichSheet.Copy after:=WhichSheet
'The duplicated sheet will definitely be the next worksheet
'Use the index property to get a reference to the duplicated sheet
Set DuplicateSheet = ThisWorkbook.Sheets(ThisWorkbook. _
    Sheets(WhichSheet.Name).Index + 1)
DuplicateSheet.Name = WhichSheet.Name & " Duplicate"

'Restore the visibility state
WhichSheet.Visible = CopySheetVisible
If Not NextSheet Is Nothing Then
    NextSheet.Visible = NextSheetVisible
End If

End Function

I recommend using the second function. Which do you like better?

If you know any other cool methods to do this, do let me know in the comments below. Also, don't forget to let me know, if you find any bugs.

Downloads

Here is a WorkBook with these functions, and examples: