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

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.

I faced a similar issue while copying a sheet to another workbook. I prefer to avoid using 'activesheet' though as it has caused me issues in the past. Hence I wrote a function to perform this inline with my needs. I add it here for those who arrive via google as I did:

The main issue here is that copying a visible sheet to the last index position results in Excel repositioning the sheet to the end of the visible sheets. Hence copying the sheet to the position after the last visible sheet sorts this issue. Even if you are copying hidden sheets.

Function Copy_WS_to_NewWB(WB As Workbook, WS As Worksheet) As Worksheet
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook
    
    Dim WSInd As Integer: WSInd = 1
    Dim CWS As Worksheet
    
    'Determine the index of the last visible worksheet
    For Each CWS In WB.Worksheets
        If CWS.Visible Then If CWS.Index > WSInd Then WSInd = CWS.Index
    Next CWS
    
    WS.Copy after:=WB.Worksheets(WSInd)
    Set Copy_WS_to_NewWB = WB.Worksheets(WSInd + 1)

End Function

To use this function for the original question (ie in the same workbook) could be done with something like...

Set test = Copy_WS_to_NewWB(Workbooks(1), Workbooks(1).Worksheets(1))
test.name = "test sheet name"

---

EDIT 04/11/2020 from –user3598756 Adding a slight refactoring of the above code

Function CopySheetToWorkBook(targetWb As Workbook, shToBeCopied As Worksheet, copiedSh As Worksheet) As Boolean
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook

    Dim lastVisibleShIndex As Long
    Dim iSh As Long

    On Error GoTo SafeExit
    
    With targetWb
        'Determine the index of the last visible worksheet
        For iSh = .Sheets.Count To 1 Step -1
            If .Sheets(iSh).Visible Then
                lastVisibleShIndex = iSh
                Exit For
            End If
        Next
    
        shToBeCopied.Copy after:=.Sheets(lastVisibleShIndex)
        Set copiedSh = .Sheets(lastVisibleShIndex + 1)
    End With
    
    CopySheetToWorkBook = True
    Exit Function
    
SafeExit:
    
End Function

other than using different (more descriptive?) variable names, the refactoring manily deals with:

1. turning the Function type into a `Boolean while including returned (copied) worksheet within function parameters list this, to let the calling Sub hande possible errors, like

    Dim WB as Workbook: Set WB = ThisWorkbook ' as an example
    Dim sh as Worksheet: Set sh = ActiveSheet ' as an example
    Dim copiedSh as Worksheet
    If CopySheetToWorkBook(WB, sh, copiedSh) Then
        ' go on with your copiedSh sheet
    Else
        Msgbox "Error while trying to copy '" & sh.Name & "'" & vbcrlf & err.Description
    End If
   

2. having the For - Next loop stepping from last sheet index backwards and exiting at first visible sheet occurence, since we're after the "last" visible one

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.