Add sheet to end
 

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

Add sheet to end
 

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


--
Don Guillett
SalesAid Software

"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

Add sheet to end
 

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

Add sheet to end
 

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

Add sheet to end
 

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

Add sheet to end
 

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