excel - VBA Copy Sheet to End of Workbook (with Hidden Worksheets)

Question

I want to copy a sheet and add it to the end of all current sheets (regardless of whether the sheets are hidden).

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = "copied sheet!"

This works fine, except, when there are hidden sheets, the new sheet is only inserted after the last visible worksheet, so the name command renames the wrong sheet.

I have tried variations of the following to get a reference to the newly copied WorkSheet but none were successful and/or valid code.

Dim test As Worksheet
Set test = Sheets(1).Copy(After:=Sheets(Sheets.Count))
test.Name = "copied sheet!"

Answer 1

Try this

Sub Sample()
    Dim test As Worksheet
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    Set test = ActiveSheet
    test.Name = "copied sheet!"
End Sub

Looking back at this, a better approach would be

Set test = Sheets(Sheets.Count)

As correctly mentioned in the comments below, there are lot of things that needs to be considered when copying and renaming a sheet. Would recommend checking the other answers as well.