VBA Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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...
 
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
 
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?!?!?
 
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.
 
Back
Top