Add sheet to end

[Guest]

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

 

[Don Guillett]

Worksheets.Add after:=Worksheets(Worksheets.C­ount)

 

[Paul]

Hi Patti,

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

moves the newly created sheet to the end.

Hope this helps!!

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

Thanks Tom!

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