Get Handle on last worksheet copied by Worksheet.Copy

Question

I'm trying to get a handle to the worksheet that was created by a copy operation. following code used to work:

Dim wsTempl As Worksheet, pageCount as Long
Set wsTempl = Sheets("Template")
For pageCount = 1 To 5
   wsTempl.Copy After:=Sheets(Sheets.Count)
   Set ws = Sheets(Sheets.Count)
   ws.Name = "p" & pageCount 
Next

But stopped when adding VeryHidden worksheets to the workbook. now my Sheets(Sheets.Count) is getting the VeryHidden sheet instead of the sheet I added last.

Of course I could use

Set ws = Sheets(wsTempl.Name & " (2)")
ws.Name = "p" & pageCount

But that seems so ugly, is this really the only way? Can somebody think of another way?

to replicate the issue:

Open a New workbook, Name the first Sheet 'Template' & delete the other sheets
alt-f11 - insert code module & paste the above code
F5 should show you that it works.
insert a worksheet, using the worksheet tabs drag it to the end of collection
set it to VeryHidden in VBA IDE
F5 again, the first code listing should fail

Reason seems to be that the Copy After:= does not copy after VeryHidden Sheets, thus the part of the code to rename the sheet always renames the VeryHidden Sheet

To break the 2nd code listing, just name your template: 'Template (3)' and update your code. Copy will now create 'Template (4)' & the code is broken. Ok ok, we could just keep a rule that we should name our template 'Template (x)' with x > 1 — Vincent De Smet
I meant: ".. we could just have a naming convention not to name our template 'Template (x)' with x > 1" — Vincent De Smet
Also make sure to delete the extra sheets in between runs or it will fail as sheets already exist. — Vincent De Smet

Steve Homer Steve Homer · Accepted Answer · 2009-08-08T15:01:09

The Copy method of a worksheet makes the newly created sheet active so you should be able to do this.


Dim wsTempl As Worksheet, i as int
Set wsTempl = Sheets("Template")
For i = 1 To 5
   wsTempl.Copy After:=Sheets(Sheets.Count)
   Set ws = ThisWorkbook.ActiveSheet
   ws.Name = "p" & pageCount 
Next

Get Handle on last worksheet copied by Worksheet.Copy

1 Answers