Add new worksheet and assign it to an object variable?

T

Thief_

I want to copy a worksheet and place it after an existing worksheet, then
assign the new worksheet to an object variable but can't get my head around
the code today. Here is what I have so far:

Set NewWS =
Worksheets(BackupWS).Copy(After:=Sheets("Summary"))
NewWS.Name = ws.Name

The above is obviously flawed as it doesn't work for me.

XL2002 SP2
Win2K Pro
 
O

OJ

Hi,
try this....

Worksheets(BackupWS).Copy , Sheets("Summary")
Set NewWS = Activesheet

Hth
OJ
 
G

Guest

Dim WS As Worksheet
Set WS = Worksheets.Add(After:=Worksheets(Worksheets.Count))

With WS
.Name = "MynewSheet"
''' more such stuff, eg
With .Range("C6: K20")
.Interior.colorindex = 34
End With

End With


Patrick Molloy
Microsoft Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Worksheets To New Workbook 2
copying a template sheet and inputting a formula into another sheetthat references the new sheet 1
Copy every worksheet 2
Assigning worksheet to worksheet variable 3
Create a new worksheet and name it 1
Macro to create a new worksheet that is a copy of an existing one? 3
Type Mismatch error when trying to generically set ComboBox to aComboBox variable 7
Assign a worksheet name to a variable 3

Top