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

excel, vba

Solution

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.

Problem

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!" ```

Original source