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.

Answer 1

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

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 ;-)

Answer 3

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.

Answer 4

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, lastSheet As Worksheet
    Dim lastIsVisible As Boolean

    If destinationWorkbook Is Nothing Then Set destinationWorkbook = sourceSheet.Parent

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

    lastIsVisible = lastSheet.Visible
    lastSheet.Visible = True

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

    If Not lastIsVisible Then lastSheet.Visible = False

    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 CopySheet2(ByRef sourceSheet As Worksheet, Optional ByRef beforeSheet As Worksheet, Optional ByRef afterSheet As Worksheet) As Worksheet

    Dim destinationWorkbook As Workbook
    Dim newSheet As Worksheet, lastSheet As Worksheet
    Dim lastIsVisible As Boolean

    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

    lastIsVisible = lastSheet.Visible
    lastSheet.Visible = True

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

    If Not lastIsVisible Then lastSheet.Visible = False

    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 CopySheet2 = newSheet

End Function

Answer 5

As already mentionned here, copy/paste the sheet to the very left (index = 1), then assign it to a variable, then move it where you would like. Pasting the Sheet Before means you do not need to verify and potentially unhide any sheet.

I am unable to test this right now but I do not see why it would not work. :)

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

    SourceWorksheet.Copy Before:= AfterDestinationWorksheet.Parent.Sheets(1)

    Dim NewWorksheet as Worksheet
    Set NewWorksheet = AfterDestinationWorksheet.Parent.Sheets(1)

    NewWorksheet.Move After:= AfterDestinationWorksheet 

    Return NewWorksheet

End Function

Answer 6

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.