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
|