Get a pen on the last worksheet copied to Worksheet.Copy

Question

I am trying to get a handle to the worksheet created by the copy. The 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 files to the workbook. now my sheets (Sheets.Count) get a VeryHidden sheet instead of the last sheet I added.

Of course I could use

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

But it seems so ugly, is this really the only way? Can anyone think of something else?

to reproduce the problem:

• Open a new book, name the first sheet "Template" and delete the remaining sheets.
• alt-f11 - paste the module code and paste the code above.
• F5 should show you that it works.
• insert the worksheet using the tabs on the worksheet, drag it to the end of the collection.
• VeryHidden VBA IDE
• F5,

, Copy After: = VeryHidden Sheets, VeryHidden Sheet

Answer 1

, .

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