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:
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
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