excelvbaworksheet

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 
  • Dim sht 

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