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:
- 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.
- 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
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.
Here is a WorkBook with these functions, and examples: