I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

try this
Sheets("201").Copy after:=Sheets(Sheets.Count)

"Elise148" wrote:

I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

That didn't work. It came up with an error saying "runtime error '1004': copy
method of worksheet class failed" Thanks for the suggestion though Mike.

"Mike" wrote:

try this
Sheets("201").Copy after:=Sheets(Sheets.Count)

"Elise148" wrote:

I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

I'm sorry try this at the begging of code
On Error Resume Next


"Elise148" wrote:

That didn't work. It came up with an error saying "runtime error '1004': copy
method of worksheet class failed" Thanks for the suggestion though Mike.

"Mike" wrote:

try this
Sheets("201").Copy after:=Sheets(Sheets.Count)

"Elise148" wrote:

I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

On Jul 2, 1:36 pm, Elise148
wrote:
I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

What is the error message? I suspect since you are renaming
ActiveSheet you are actually renaming your source sheet by mistake.

Try:
Sheets("201").Copy After:=Sheets(sheets.count)
sheets(sheets.count).name = InputBox("Enter the number of the new
distributor.")

Peter

Possibly...
http://support.microsoft.com/default...b;en-us;210684
"Copying Worksheet Programmatically Causes Run-Time Error 1004 in Excel"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Elise148"
wrote in message
I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

Thanks Peter...That seemed to work...at least, for now it's working... :)

" wrote:

On Jul 2, 1:36 pm, Elise148
wrote:
I'm trying to copy a worksheet and move and rename it to the end of the
workbook. This is the VBA code I have...

Sheets("201").Select
N = Sheets.Count
Sheets("201").Copy After:=Sheets(N)
ActiveSheet.Name = InputBox("Enter the number of the new distributor.")

It works for several times, but then an error comes up and it highlights the
third row. I don't understand! Any ideas?!?!?

What is the error message? I suspect since you are renaming
ActiveSheet you are actually renaming your source sheet by mistake.

Try:
Sheets("201").Copy After:=Sheets(sheets.count)
sheets(sheets.count).name = InputBox("Enter the number of the new
distributor.")

Peter

This works for me just fine every time: (well, i stopped at 20
sheets, but i think it'll be okay!)

Sub SheetsCount()
Dim i As Integer
i = Sheets.Count
Sheets("Sheet1").Copy After:=Sheets(i)
End Sub

....code is in a module, not a Sheet object.