VBA Problems

E

Elise148

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?!?!?
 
E

Elise148

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.
 
M

Mike

I'm sorry try this at the begging of code
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.
Click to expand...
 
P

par_60056

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?!?!?
Click to expand...

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
 
J

Jim Cone

Possibly...
http://support.microsoft.com/default.aspx?scid=kb;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" <[email protected]>
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?!?!?
 
D

dksaluki

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.
 
Top