Add new worksheet and assign it to an object variable?

  • Thread starter Thread starter Thief_
  • Start date Start date
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
 
Hi,
try this....

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

Hth
OJ
 
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
 
Back
Top