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_
|