Copy sheet and get resulting sheet object?

Question

Is there any easy/short way to get the worksheet object of the new sheet you get when you copy a worksheet?

ActiveWorkbook.Sheets("Sheet1").Copy after:=someSheet

It turns out that the .Copy method returns a Boolean instead of a worksheet object. Otherwise, I could have done:

set newSheet = ActiveWorkbook.Sheets("Sheet1").Copy after:=someSheet

So, I wrote some 25 lines of code to get the object. List all sheets before the copy, list all sheets after, and figure out which one is in the second list only.

I am looking for a more elegant, shorter solution.

Fluoridate

Are you copying to a new workbook, or within the same workbook? – Dispend

@Tim: withing the same workbook – Fluoridate

"If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet." -- msdn.microsoft.com/en-us/library/ff837784.aspx. So are specifying before or after, and if so, why are you having problems? – Mango

@Remou: I do use an after:= I had left it out of my code to make it shorter, and wasn't aware of the implication (which is not really relevant actually). I've edited my question. – Fluoridate

Of course it is relevant, see @Tim's reply. – Mango

@Remou: yep, now I understand... :-) – Fluoridate

Answer 1

Dim sht 

With ActiveWorkbook
   .Sheets("Sheet1").Copy After:= .Sheets("Sheet2")
   Set sht = .Sheets(.Sheets("Sheet2").Index + 1)
End With

Dispend

AFAIK, this only works if the source worksheet is visible and if the visible order of the tabs matches the worksheets collection order (.Index), which is not always the case. The Before and After arguments appear to refer to the visible order and the Index property only gets set to match that if they are visible. At least that's what my testing/debugging is showing me today in Excel. :( – Pallor

@RBarry - can you describe a case where the code produces the "wrong" result? If your source worksheet is hidden then you're going to have to unhide the copy if you want it visible, but otherwise I can't see a problem with the approach I suggested. – Dispend

@TimWilliams if you copy a sheet at index 1 and select to copy it after sheet with index 1 and then hide it, then it works. But if you do the same again, the new sheet will be inserted at index 3(!!) after the hidden sheet. so this approach works only one time if you want to hide the sheet after the copy. – Swift

I think there are definitely issues with my answer here, as you point out (also @Pallor and @alrm3000). I guess I should beef it up a bit at some point. – Dispend

Here is a preliminary attempt at said beefing. – Wallywalnut

Answer 2

I believe I have finally nailed this issue - it's been driving me nuts, also! It really would have been nice if MS made Copy return a sheet object, same as the Add method...

The thing is, the index which VBA allocates a newly copied sheet is actually not determined... as others have noted, it very much depends on hidden sheets. In fact, I think the expression Sheets(n) is actually interpreted as "the nth visible sheet". So unless you write a loop testing every sheet's visible property, using this in code is fraught with danger, unless the workbook is protected so users cannot mess with sheets visible property. Too hard...

My solution to this dilemma is:

1. Make the LAST sheet visible (even if temporary)
2. Copy AFTER that sheet. It MUST have index Sheets.Count
3. Hide the former last sheet again, if required - it will now have index Sheets.Count-1
4. Move the new sheet to where you really want it.

Here's my code - which now seems to be bullet-proof...

Dim sh as worksheet
Dim last_is_visible as boolean

With ActiveWorkbook
    last_is_visible = .Sheets(.Sheets.Count).Visible
    .Sheets(Sheets.Count).Visible = True
    .Sheets("Template").Copy After:=.Sheets(Sheets.Count)
    Set sh=.Sheets(Sheets.Count)
    if not last_is_visible then .Sheets(Sheets.Count-1).Visible = False 
    sh.Move After:=.Sheets("OtherSheet")
End With

In my case, I had something like this (H indicating a hidden sheet)

1... 2... 3(H)... 4(H)... 5(H)... 6... 7... 8(H)... 9(H)

.Copy After:=.Sheets(2) actually creates a new sheet BEFORE the next VISIBLE sheet - ie, it became the new index 6. NOT at index 3, as you might expect.

Hope that helps ;-)

Mihrab

I believe this should be marked as the correct answer. – Aeciospore

No need to consider wether a sheet is visible or not, if instead of pasting After:=.Sheets(Sheets.Count) you paste Before:=.Sheets(1). Then Set Sh = .sheets(1) will always work, wether some other sheets are hidden or not; as soon as the sheet you copy/paste is visible of course. – Peepul

@Peepul I know it's been almost a year, but - for the benefit of the future readers - my testing appears to prove otherwise. – Materials

Note: Tigregalis made this solution into a reusable function. – Materials

Worth noting that the .Move method treats hidden sheets same as .Copy. So whilesh gets assigned to the right sheet, at least, sh.Move after:=Sheets(2) still ends up assigning it to index 6 instead of 3. If exact ordering including hidden sheets were important for whatever reason, you'd have to check the visibility of Sheets(3) before the move and unhide/rehide if necessary. Which got me thinking: this answer already toggles visibility once if necessary, so why not just toggle .Sheets("OtherSheet") right from the start instead? – Wallywalnut

Answer 3

Another solution I used would be to copy the sheet to a place where you know its index, aka first. There you can easily have a reference to it for whatever you need, and after that you can move it freely to where you want.

Something like this:

Worksheets("Sheet1").Copy before:=Worksheets(1)
set newSheet = Worksheets(1)
newSheet.move After:=someSheet

Dottie

Definitely the less smelly option! – Peepul

This will also not work if there's a hidden sheet before the first visible sheet Sheet1: Worksheets(1) will return the hidden sheet, but .Copy Before:=Worksheets(1) will confusingly insert the new sheet after the hidden one. So newSheet will refer to the hidden sheet, not the newly created sheet... – Materials

This is True, I am updating my answer to reflect this. Looks like Excel Interop (VB.Net) behaves differently.. – Peepul

Answer 4

UPDATE:

Dim ThisSheet As Worksheet
Dim NewSheet As Worksheet
Set ThisSheet = ActiveWorkbook.Sheets("Sheet1")
ThisSheet.Copy
Set NewSheet = Application.ActiveSheet

Kelley

Won't use it, but nice idea as well! – Fluoridate

Yeah, @Tim Williams answer is a much better solution. – Kelley

This relies on the newly created sheet being activated (and so available as ActiveSheet). Relying on ActiveSheet/Selection is usually frowned upon (as this makes the macros fragile and prone to misbehavior when something other than the macro is running on the system). For this reason I prefer Trevor Norman's answer. – Materials

Answer 5

Updated with suggestions from Daniel Labelle:

To handle possible hidden sheets, make the source sheet visible, copy it, use the ActiveSheet method to return the reference to the new sheet, and reset the visibility settings:

Dim newSheet As Worksheet
With ActiveWorkbook.Worksheets("Sheet1")
    .Visible = xlSheetVisible
    .Copy after:=someSheet
    Set newSheet = ActiveSheet
    .Visible = xlSheetHidden ' or xlSheetVeryHidden
End With

Brutal

This is PaulStock's answer improved to deal with the "invisible source sheet" case. As the other answer, this relies on the newly created sheet being activated (and so available as ActiveSheet). Relying on ActiveSheet/Selection is usually frowned upon (as this makes the macros fragile and prone to misbehavior when something other than the macro is running on the system). For this reason I prefer Trevor Norman's answer. – Materials

Answer 6

I realise this post is over a year old, but I came here looking for an answer to the same issue regarding copying sheets and unexpected results caused by hidden sheets. None of the above really suited what I wanted mainly because of the structure of my workbook. Essentailly it has a very large number of sheets and what is displayed is driven by a user selecting the specific functionality, plus the order of the visible sheets was importnat to me so i didnt want to mess with those. So my end solution was to rely on Excels default naming convention for copied sheets, and explictly rename the new sheet by name. Code sample below (as an aside, my workbook has 42 sheets and only 7 are permanently visible, and the after:=Sheets(Sheets.count) put my copied sheet in the middle of the 42 sheets, depending on what sheets are visible at the time.

        Select Case DCSType
        Case "Radiology"
            'Copy the appropriate Template to a new sheet at the end
            TemplateRAD.Copy after:=Sheets(Sheets.count)
            wsToCopyName = TemplateRAD.Name & " (2)"
            'rename it as "Template"
            Sheets(wsToCopyName).Name = "Template"
            'Copy the appropriate val_Request to a new sheet at the end
            valRequestRad.Copy after:=Sheets(Sheets.count)
            'rename it as "val_Request"
            wsToCopyName = valRequestRad.Name & " (2)"
            Sheets(wsToCopyName).Name = "val_Request"
        Case "Pathology"
            'Copy the appropriate Template to a new sheet at the end
            TemplatePath.Copy after:=Sheets(Sheets.count)
            wsToCopyName = TemplatePath.Name & " (2)"
            'rename it as "Template"
            Sheets(wsToCopyName).Name = "Template"
            'Copy the appropriate val_Request to a new sheet at the end
            valRequestPath.Copy after:=Sheets(Sheets.count)
            wsToCopyName = valRequestPath.Name & " (2)"
            'rename it as "val_Request"
            Sheets(wsToCopyName).Name = "val_Request"
    End Select

Anyway, posted just in case its useful to anyone else

Forefoot

While I think Trevor Norman's answer is the truly correct one, this workaround you're proposing is definitely the best method, provided one doesn't mind to rely on MS not to change their naming conventions in the future (which I'm not sure I do...). – Aeciospore

Answer 7

This question is really old, but as there were some activity here not so long time ago and it still gave me all the answers I needed 10 years later, I'd like to share the way I did it.

After reading this thread, I found Tigregalis'answer really interesting, even if I prefer Ama's solution. But none of them was reflecting original Excel behavior with the choice of copying before/after or to a new workbook. As I needed it, I wrote down my own function, and to make it still closer from Excel's one, I made it able to handle Sheets and not just Worksheets.

For those interested, here is my code :

Function CopySheet(ByVal InitSh As Object, Optional ByVal BeforeSh As Object, Optional ByVal AfterSh As Object) As Object
'Excel doesn't provide any reliable way to get a pointer to a newly copied sheet. This function allows to make it
'Arguments: - InitSh : The sheet we want to copy
'           - BeforeSh : The sheet before the one we want the copy to be placed
'           - AfterSh : The sheet after the one we want the copy to be placed
'Return   : - Returns the newly copied sheet. If BeforeSh and AfterSh are not givent to the sub, the sheet is created in a new workbook. In the case both are given, BeforeSh is used
'             To beknown : if the InitSh is not visible, the new one won't be visible except if InitWks is the first of the workbook !

    Dim isBefore As Boolean
    Dim isAfter As Boolean
    Dim Wkb As Workbook
    
    'If there is before or after, we need to know the workbook where the new sheet is copied, if not we need to set up a new workbook
    If Not BeforeSh Is Nothing Then
        isBefore = True
        Set Wkb = BeforeSh.Parent
    ElseIf Not AfterSh Is Nothing Then
        isAfter = True
        Set Wkb = AfterSh.Parent
    Else
        Set Wkb = Application.Workbooks.Add(xlWBATWorksheet)
    End If

    'To be able to find the new worksheet, we need to make sure the first sheet of the destination workbook is visible and make the copy before it
    Dim FirstWksVisibility As XlSheetVisibility
    FirstWksVisibility = Wkb.Sheets(1).Visible
    Wkb.Sheets(1).Visible = xlSheetVisible

    InitSh.Copy before:=Wkb.Sheets(1)

    'Restore the initial visibility of the first worksheet of the workbook, that is now the sheet number 2 as we copied one in front of it
    Wkb.Sheets(2).Visible = FirstWksVisibility
    
    'Finaly, move the sheet accordingly to otpional arguments BeforeWks or AfterWks
    Dim TempSh As Object
    Set TempSh = Wkb.Sheets(1)
    If isBefore Then
        TempSh.Move before:=BeforeSh
    ElseIf isAfter Then
        TempSh.Move after:=AfterSh
    Else
        'If no optional arguments, we made a new workbook and we need to erase the blank worksheet that was created with it if the new sheet is visible (we cant if it's not visible)
        If TempSh.Visible = xlSheetVisible Then
            Dim Alert As Boolean
            Alert = Application.DisplayAlerts
            Application.DisplayAlerts = False
            Wkb.Sheets(2).Delete
            Application.DisplayAlerts = Alert
        End If
    End If
    
    Set CopySheet = TempSh
End Function

I tried to test my code extensively with worksheets and charts, and I think it does what it was designed for. The only thing to note is that copied sheet won't be visible if the source one was not, EXCEPT if the source one was the first sheet of the workbook.

Klinges

Answer 8

This should be a comment in response to @TimWilliams, but it's my first post so I can't comment.

This is an example of the problem @RBarryYoung mentioned, related to hidden sheets. There is a problem when you try to put your copy after the last sheet and the last sheet is hidden. It seems that, if the last sheet is hidden, it always retains the highest index, so you need something like

Dim sht As Worksheet

With ActiveWorkbook
   .Sheets("Sheet1").Copy After:=.Sheets(.Sheets.Count)
   Set sht = .Sheets(.Sheets.Count - 1)
End With

Similar situation when you try to copy before a hidden first sheet.

Lucite

Answer 9

Based on Trevor Norman's method, I've developed a function for copying a sheet and returning a reference to the new sheet.

1. Unhide the last sheet (1) if not visible
2. Copy the source sheet (2) after the last sheet (1)
3. Set the reference to the new sheet (3), i.e. the sheet after the last sheet (1)
4. Hide the last sheet (1) if necessary

Code:

Function CopySheet(ByRef sourceSheet As Worksheet, Optional ByRef destinationWorkbook As Workbook) As Worksheet

    Dim newSheet As Worksheet
    Dim lastSheet As Worksheet
    Dim lastIsVisible As XlSheetVisibility

    If destinationWorkbook Is Nothing Then Set destinationWorkbook = sourceSheet.Parent

    With destinationWorkbook
        Set lastSheet = .Worksheets(.Worksheets.Count)
    End With

    ' store visibility of last sheet
    lastIsVisible = lastSheet.Visible
    ' make the last sheet visible
    lastSheet.Visible = xlSheetVisible

    sourceSheet.Copy After:=lastSheet
    Set newSheet = lastSheet.Next

    ' restore visibility of last sheet
    lastSheet.Visible = lastIsVisible

    Set CopySheet = newSheet

End Function

This will always insert the copied sheet at the end of the destination workbook.

After this, you can do any moves, renames, etc.

Usage:

Sub Sample()

    Dim newSheet As Worksheet

    Set newSheet = CopySheet(ThisWorkbook.Worksheets("Template"))

    Debug.Print newSheet.Name

    newSheet.Name = "Sample" ' rename new sheet
    newSheet.Move Before:=ThisWorkbook.Worksheets(1) ' move to beginning

    Debug.Print newSheet.Name

End Sub

Or if you want the behaviour/interface to be more similar to the built-in Copy method (i.e. before/after), you could use:

Function CopySheetTo(ByRef sourceSheet As Worksheet, Optional ByRef beforeSheet As Worksheet, Optional ByRef afterSheet As Worksheet) As Worksheet

    Dim destinationWorkbook As Workbook
    Dim newSheet As Worksheet
    Dim lastSheet As Worksheet
    Dim lastIsVisible As XlSheetVisibility

    If Not beforeSheet Is Nothing Then
        Set destinationWorkbook = beforeSheet.Parent
    ElseIf Not afterSheet Is Nothing Then
        Set destinationWorkbook = afterSheet.Parent
    Else
        Set destinationWorkbook = sourceSheet.Parent
    End If

    With destinationWorkbook
        Set lastSheet = .Worksheets(.Worksheets.Count)
    End With

    ' store visibility of last sheet
    lastIsVisible = lastSheet.Visible
    ' make the last sheet visible
    lastSheet.Visible = xlSheetVisible

    sourceSheet.Copy After:=lastSheet
    Set newSheet = lastSheet.Next

    ' restore visibility of last sheet
    lastSheet.Visible = lastIsVisible

    If Not beforeSheet Is Nothing Then
        newSheet.Move Before:=beforeSheet
    ElseIf Not afterSheet Is Nothing Then
        newSheet.Move After:=afterSheet
    Else
        newSheet.Move After:=sourceSheet
    End If

    Set CopySheetTo = newSheet

End Function

Monogenetic

Strictly speaking, Visible/lastIsVisible should be of type XlSheetVisibility, not Boolean. Great answer anyway! – Materials

Answer 10

As already mentioned here, copy/paste the sheet to the very left (index = 1), then assign it to a variable, then move it where you would like.

Function CopyWorksheet(SourceWorksheet As Worksheet, AfterDestinationWorksheet As Worksheet) As Worksheet

    Dim DestinationWorkbook As Workbook
    Set DestinationWorkbook = AfterDestinationWorksheet.Parent

    Dim FirstSheetVisibility As XlSheetVisibility
    FirstSheetVisibility = DestinationWorkbook.Sheets(1).Visible

    DestinationWorkbook.Sheets(1).Visible = xlSheetVisible
    SourceWorksheet.Copy Before:=DestinationWorkbook.Sheets(1)
    DestinationWorkbook.Sheets(2).Visible = FirstSheetVisibility

    Dim NewWorksheet As Worksheet
    Set NewWorksheet = DestinationWorkbook.Sheets(1)

    NewWorksheet.Move After:=AfterDestinationWorksheet

    Set CopyWorksheet = NewWorksheet

End Function

Peepul

The new approach seems to be similar to what Trevor Norman suggested, but wrapped in a reusable function. Removed my previous comment and upvoted - thanks for the update! – Materials

Answer 11

It is correct that hidden worksheets cause the new worksheet index to be non-sequential on either side of the source worksheet. I found that Rachel's answer works if you're copying before. But you'd have to adjust it if you're copying after.

Once the model is visible and copied, the new worksheet object is simply the ActiveSheet whether you copy the source before or after.

As a preference, you could replace:

Set newSheet = .Previous with Set newSheet = Application.ActiveSheet.

Hope this is helpful to some of you.

Thorax

For the future readers, this appears to be a comment on another answer here - https://mcmap.net/q/404799/-copy-sheet-and-get-resulting-sheet-object - which has been updated since. – Materials

Answer 12

I had the same requirement and came to this thread while looking for an answer. While checking out various options, found that, a easy way to access the new sheet is, using the chain of references that Excel stores (sample below). It seems like Excel maintains a linked list kind of thing w.r.t the sheet references.

'Example:
ActiveWorkbook.Sheets("Sheet1").Copy After:=someSheet
set newSheet = someSheet.Next

Similarly for the sheet inserted 'before' another sheet...

ActiveWorkbook.Sheets("Sheet1").Copy Before:=someSheet
set newSheet = someSheet.Previous

Works even if the source sheet is hidden. If the source sheet is hidden, the worksheet is copied, but the new sheet remains hidden too!

Derwood

This is only a more elegant way to write out Tim Williams accepted answer above. Glad to learn the terminology, but it's important to note that it will fail in exactly the same ways discussed in the comments to that answer. – Wallywalnut

Answer 13

I've been trying to create a reliable generic "wrapper" function for the sheet.Copy method for re-use across multiple projects for years.

I've tried several of the approaches here and I've found only Mark Moore's answer to be a reliable solution across all scenarios. Ie the one using the "Template (2)" name to identify the new sheet.

In my case, any solution using the "ActiveSheet method" was useless as in some instances the target workbook was in a non-Active or hidden Workbook.

Similarly, some of my Workbooks have hidden sheets intermixed with visible sheets in various locations; at the beginning, in the middle, at the end; and therefore I found the solutions using the Before: and After: options also unreliable depending on the ordering of the visible and hidden sheets, along with the additional factor when the source sheet is also hidden.

Therefore after several re-writes, I've ended up with the following wrapper function:

'***************************************************************************
'This is a wrapper for the worksheet.Copy method.
'
'Used to create a copy of the specified sheet, optionally set it's name, and return the new
' sheets object to the calling function.
'
'This routine is needed to predictably identify the new sheet that is added. This is because
' having Hidden sheets in a Workbook can produce unexpected results in the order of the sheets,
' eg when adding a hidden sheet after the last sheet, the new sheet doesn't always end up
' being the last sheet in the Worksheets collection.
'***************************************************************************
Function wsCopy(wsSource As Worksheet, wsAfter As Worksheet, Optional ByVal sNewSheetName As String) As Worksheet

    Dim Ws              As Worksheet

    wsSource.Copy After:=wsAfter
    Set Ws = wsAfter.Parent.Sheets(wsSource.Name & " (2)")

    'set ws Name if one supplied
    If sNewSheetName <> "" Then
        Ws.Name = sNewSheetName
    End If
    Set wsCopy = Ws
End Function

NOTE: Even this solution will have issues if the source sheet's Name is more than 27 chars, as the maximum sheet name is 31, but that is usually under my control.

Personally

Answer 14

Old post but wasn't sure about unhiding sheets or adding suffixes to names.

This is my approach:

Sub DuplicateSheet()
    Dim position As Integer
    Dim wbNewSheet As Worksheet
    position = GetFirstVisiblePostion

    ThisWorkbook.Worksheets("Original").Copy Before:=ThisWorkbook.Sheets(position)
    Set wbNewSheet = ThisWorkbook.Sheets(position)

    Debug.Print "Duplicated name:" & wbNewSheet.Name, "Duplicated position:" & wbNewSheet.Index

End Sub

Function GetFirstVisiblePostion() As Integer
    Dim wbSheet As Worksheet
    Dim position As Integer
    For Each wbSheet In ThisWorkbook.Sheets
        If wbSheet.Visible = xlSheetVisible Then
            position = wbSheet.Index
            Exit For
        End If
    Next
    GetFirstVisiblePostion = position
End Function

Herwig

Answer 15

Wanted to share my simple solution to this with the following code

Sub copy_sheet(insheet As String, newsheet As String)
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Sheets(newsheet).Delete
ThisWorkbook.Sheets(insheet).Copy before:=ThisWorkbook.Sheets(1)
For Each ws In ThisWorkbook.Worksheets
    If (InStr(ws.Name, insheet) > 0 And InStr(ws.Name, "(") > 0) Then
        ThisWorkbook.Sheets(ws.Name).Name = newsheet
        Exit For
    End If
Next
Application.DisplayAlerts = True
End Sub

Whenever you copy a sheet, the resulting "copied" sheet ALWAYS has the name of the original sheet, and a bracketed number. As long as none of your original sheets contain bracketed number names, this will work 100% of the time.

It copies the sheet, then loops through all sheet names looking for one that 1) contains the original name and 2) has a bracketed number, and then renames the sheet

Ivoryivorywhite

Answer 16

I had the same problem as OP, but with the addition of some hidden and very hidden sheets.

Finding the last sheet by using something like {set last_sheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)} does not work because Excel does not count the hidden worksheets, so the position number {last_sheet.Index + 1} is too high and makes an error.

Instead I made a loop to find the position:

Dim w as Workbook, s as Worksheet, template_sheet as worksheet, last_sheet as Worksheet, new_sheet as Worksheet
    
' find the position of the last sheet
  For Each s in w.Workbooks
    If s.Visible = xlSheetVisible then
      Set last_sheet = s
    End if
  Next
    
' make the sheet to be copied visible, copy it and hide it again
  w.Worksheets("template_sheet").Visible = xlHidden
  w.Worksheets("template_sheet").Copy After:=last_sheet
  w.Worksheets("template_sheet").Visible = xlVeryHidden
        
' reference the new sheet that was just added
  Set new_sheet = Worksheets(last_sheet.index + 1)

Backset

Answer 17

After comparing several of the answers already here I noticed that even the best of them always rely on two sheet operations — one to first copy the sheet and then a second to move it to the desired position — but none of them account for the move operation also running into the same issues with hidden sheets if the exact ordering of your sheets, including any that are hidden, is important for some reason or another.

So here's an alternative methodology to let you copy a target sheet to an exact position w.r.t. to a workbook's sheet indices regardless of which of them are hidden or not. It wraps the basic logic of Tim Williams' answer as simplified by Sat's syntax with conditionals hinging on Trevor Norman's insight that whatever Excel is doing under the hood always wants to copy the sheet to a postion immediately before a sheet that is visible:

Function CopySheetBefore(ByRef copyTarget As Worksheet, ByRef positionTarget As Worksheet) As Worksheet
Dim visibilityState As XlSheetVisibility, VeryHiddenTarget As Boolean

'record starting visibilities and reset as needed:
visibilityState = positionTarget.Visible
If Not (copyTarget Is positionTarget) Then
    VeryHiddenTarget = (copyTarget.Visible = xlSheetVeryHidden)
Else
    'leave it False to preclude redundant action below...\/
End If
positionTarget.Visible = xlSheetVisible
If VeryHiddenTarget Then copyTarget.Visible = xlSheetVisible

'copy sheet and set return variable:
copyTarget.Copy before:=positionTarget
Set CopySheetBefore = positionTarget.Previous

'reset visibilities to initial states:
positionTarget.Visible = visibilityState
If copyTarget Is positionTarget Then
    CopySheetBefore.Visible = visibilityState
    Exit Function 'to preclude redundant actions below
End If
If VeryHiddenTarget Then
    copyTarget.Visible = xlSheetVeryHidden
    CopySheetBefore.Visible = xlSheetVeryHidden
End If
End Function

If you know you won't ever be trying to copy a sheet that is very hidden, the function simplifies considerably to:

Function CopySheetBefore(ByRef copyTarget As Worksheet, ByRef positionTarget As Worksheet) As Worksheet
Dim visibilityState As XlSheetVisibility

'record starting visibility and reset:
visibilityState = positionTarget.Visible
positionTarget.Visible = xlSheetVisible

'copy sheet and set return variable:
copyTarget.Copy before:=positionTarget
Set CopySheetBefore = positionTarget.Previous

'reset visibility to initial state:
positionTarget.Visible = visibilityState
If copyTarget Is positionTarget Then
    CopySheetBefore.Visible = visibilityState
End If
End Function

This will always insert the copied sheet to the index position immediately before the targeted position regardless of that position target's visibility or that of any other surrounding sheets.

If you're committed to thinking in terms of which sheet it should be placed after instead of before (or want to be able to insert to the last sheet index specifically), the logic is essentially the same, but the conditionals required to pull it off even though that last sheet might be hidden are a bit more complex:

Function CopySheetAfter(ByRef copyTarget As Worksheet, ByRef positionTarget As Worksheet) As Worksheet
Dim visibilityState As XlSheetVisibility
Dim CopyAfterLast as Boolean

CopyAfterLast = (positionTarget.index = sheets.count)

'record starting visibility and reset:
If CopyAfterLast Then
    visibilityState = positionTarget.Visible
    positionTarget.Visible = xlSheetVisible
Else
    visibilityState = positionTarget.Next.Visible
    positionTarget.Next.Visible = xlSheetVisible
End If

'copy sheet and set return variable:
copyTarget.Copy after:=positionTarget
Set CopySheetAfter = positionTarget.Next

'reset visibility to initial state:
If CopyAfterLast Then
    positionTarget.Visible = visibilityState
Else
    positionTarget.Next.Next.Visible = visibilityState
End If
If copyTarget Is positionTarget Then
    CopySheetAfter.Visible = visibilityState
End If
End Function

Wallywalnut

Answer 18

Upon copying , isn't the copy now the Active Sheet?

Dim TempSheetRef As Worksheet
 
Sheets("MySheet").Copy After:=Sheets(2)

Set TempSheetRef = ActiveSheet

TempSheetRef.Name = "My Copy  "

Interdenominational

Answer 19

I would like to provide a solution that does not depend on the index position of a worksheet and is unaffected by the visibility state of other worksheets. My approach is to simply determine the sheet name before a sheet gets copied. Excel's default behaviour is to simply copy the name and add a number in parentheses. I am leveraging this behavior to determine the sheet's name.

The following function "CreateCopyOfWorksheet" only requires a worksheet ("wsToBeCopied") as an input parameter and will return the copied worksheet as an object. This function calls another function "CheckIfSheetExists".

Function CreateCopyOfWorksheet(wsToBeCopied As Worksheet) As Worksheet
'This macro copies a worksheet and positions it as the last visible worksheet.
'This function returns the copied as a worksheet

Dim wsCopy As Worksheet
Dim strSheetName As String, strCopiedSheetName As String
Dim bHidden As Boolean
Dim loIndex As Long: loIndex = 2

'Unhide the worksheet that will be copied. Hidden worksheets may not be copied.
With wsToBeCopied
    If .Visible <> True Then
        bHidden = True
        .Visible = xlSheetVisible
    End If
End With

With Application.ThisWorkbook
    'Determine the name that will be applied to the copied worksheet
    Do Until CheckIfSheetExists(wsToBeCopied.Name & " (" & loIndex & ")") = False
        loIndex = loIndex + 1
    Loop
    strCopiedSheetName = wsToBeCopied.Name & " (" & loIndex & ")"
    
    'Copy worksheet and place copy at the very end of all visible sheets
    wsToBeCopied.Copy After:=.Worksheets(.Worksheets.Count)
    Set wsCopy = .Worksheets(strCopiedSheetName)
End With

'Hide the worksheet that was copied in case it was originally hidden
If bHidden = True Then
    wsToBeCopied.Visible = xlSheetVeryHidden
End If

Set CreateCopyOfWorksheet = wsCopy
End Function

Function CheckIfSheetExists(strSheetName As String) As Boolean
Dim ws As Worksheet
For Each ws In Application.ThisWorkbook.Worksheets
    If ws.Name = strSheetName Then
        CheckIfSheetExists = True
        Exit Function
    End If
Next
End Function

Commitment

Answer 20

All of these seem overly complex. Use Copybefore the first sheet (ws.Copy Before:=ThisWorkbook.Sheets(1)). Once created, it will be the active sheet. Just rename it, ThisWorkbook.ActiveSheet.Name = newSheetName, and now you can do whatever you want with it.

Dhyana