Assigning worksheet to worksheet variable

[PO]

Hi!

I'm trying to assign a copied worksheet to a worksheet variable using the
following code:

Dim sSheet As New Excel.Worksheet
Set sSheet = Sheets("Template").Copy(Before:=Sheets("Template"))

This doesn't work.

Any ideas?

TIA
PO

 

[Bob Phillips]

Mot sure, but is this not acceptable?

Worksheets("Template").Copy Before:=Worksheets("Template")
Set sSheet = ActiveSheet


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Look in the object browser at Worksheet, Copy method. You see at the bottom
that it does not show "As Object"

The copy method does not return anything, so you can not set a reference to
it. (contrast with Worksheet, OleObjects method which is shown as "As
Object" and returns the OleObjects collection for the worksheet).

The workaround. When the sheet is copied, the copy is then the activesheet.

Dim sSheet As Excel.Worksheet
Sheets("Template").Copy(Before:=Sheets("Template"))
Set sSheet = Activesheet

I wouldn't use New in the declaration.

 

[Tom Ogilvy]

When you remove the assignment, you need to remove the parentheses

Dim sSheet As Excel.Worksheet
Sheets("Template").Copy Before:=Sheets("Template")
Set sSheet = Activesheet