ExcelBanter - View Single Post

Patti

Thanks Tom!

"Tom Ogilvy" wrote:

Sure it does if you write it correctly

Set wsNew = Worksheets.Add( after:=Worksheets(Worksheets.Count))

demo'd from the immediate window:

Set wsNew = Worksheets.Add( after:=Worksheets(Worksheets.Count))
? wsNew.Name
Sheet2

--
Regards,
Tom Ogilvy

"Patti" wrote in message
...
That does help - thanks. Both suggestions work. If anyone has the time
to
explain, I am curious as to why I cannot add the sheet to the end when
using
Set:

Set wsNew = Worksheets.Add after:=Worksheets(Worksheets.Count) ' doesn't
work

wsNew.Name = InputBox("Enter New Worksheet Name")

Regards,

Patti

"Paul" wrote:

Hi Patti,

ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)

moves the newly created sheet to the end.

Hope this helps!!

"Patti" wrote in message
...
Hello. How do I get this to add the new sheet to the end of my
workbook:

Sub CopyRange()

Dim wsMain As Worksheet, wsNew As Worksheet

Set wsMain = Sheets("test")
Set wsNew = Sheets.Add
wsMain.Range("A:B").Copy wsNew.Range("A1")
wsNew.Name = InputBox("Enter New Worksheet Name")

End Sub

Thanks,

Patti