Assigning worksheet to worksheet variable
 

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(Befo=Sheets("Template") )

This doesn't work.

Any ideas?

TIA
PO

Assigning worksheet to worksheet variable
 

Mot sure, but is this not acceptable?

Worksheets("Template").Copy Befo=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)

"PO" <po wrote in message ...
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(Befo=Sheets("Template") )

This doesn't work.

Any ideas?

TIA
PO

Assigning worksheet to worksheet variable
 

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(Befo=Sheets("Template") )
Set sSheet = Activesheet

I wouldn't use New in the declaration.

--
Regards,
Tom Ogilvy



"PO" <po wrote in message ...
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(Befo=Sheets("Template") )

This doesn't work.

Any ideas?

TIA
PO

Assigning worksheet to worksheet variable
 

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

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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(Befo=Sheets("Template") )
Set sSheet = Activesheet

I wouldn't use New in the declaration.

--
Regards,
Tom Ogilvy



"PO" <po wrote in message ...
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(Befo=Sheets("Template") )

This doesn't work.

Any ideas?

TIA
PO