Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a Worksheet Name to a Cell | Excel Worksheet Functions | |||
Link variable data from one worksheet to another worksheet? | New Users to Excel | |||
Variable worksheet name (tab) linked to cell in same worksheet | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
ASSIGNING A NEW NUMBER TO A WORKSHEET EVERY TIME IT IS OPEN | Excel Worksheet Functions |