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.

Answer 2

Make the source sheet visible before copying. Then copy the sheet so that the copy also stays visible. The copy will then be the active sheet. If you want, hide the source sheet again.