Copy sheet and get resulting sheet object?

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.

Solution 1:

Dim sht 

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

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

Solution 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

Solution 4:

UPDATE:

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

Solution 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