Add new worksheet and assign it to an object variable?

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


--
|
+--Thief_
|

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


"Thief_" wrote:

> 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
>
>
> --
> |
> +--Thief_
> |
>
>
>
>