Your ActiveSheet line may be the source of the problem. Change it to

Sheets(Sheets.Count).Name = "MBA" & " " & Sheets("Sheet2").Range("A2").Value

Your 'Booking Form' sheets gets renamed under certain conditions hence you
get the

Object Not Found error...
'Run-time error: '1004':'
'Copy method of worksheet class failed'


"Sheeloo" wrote:

No, order won't matter since you are copying after the last sheet...
I tested the following in Excel 2007
Sub copySheet()
Dim i
For i = 1 To 100
Sheets("Sheet3").Copy After:=Sheets(Sheets.Count) ' error happens here
ActiveSheet.Name = "MBA" & " " & i
Next
End Sub

Run this and see when it breaks... I am not sure what the limit is on number
of sheets in Excel but you may test it...

Your code SHOULD NOT fail... most likely there is something else which is
wrong due to which it is breaking down here...


"BeSmart" wrote:

Hi Sheeloo - thanks for looking at this for me...

No, I'm not seeing any sheets named Booking Form (2) after the fail message.
The debug goes to the line of code and nothing changes with the worksheets.

I understand that an error will happen if the macro is run twice (due to the
name on the second row of the code) - I'm working on this via code that will
re-name the sheet if it already exists (I posted a separate question on that).

At the moment I'm deleting the created workbooks (e.g. MBA Wk1) before
re-running the macro to further test it.

Could the problem be due to my existing worksheets being out of number order
i.e. they a

Sheet1 (Booking Form)
Sheet4 (Lookups)
Sheet5 (Plan)
Sheet7 (Data)

Perhaps I should nominate the new worksheet to appear after Sheet7 (Data)??

Any further help would be greatly appreciated.
--
Cheers
BeSmart


"Sheeloo" wrote:

The following code should not fail..
Sheets("Booking Form").Copy After:=Sheets(Sheets.Count)

However it will fail after the following line if it is run the second time
since the sheet with the name will be existing..
ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A2").Value

Do you have sheets named like Booking Form (2), Booking Form (3) after the
code fails?


"BeSmart" wrote:

Hi All
Any help with this error message would be greatly appreciated:

I'm getting the following error when running a macro that copies one
worksheet and places it after the last worksheet, then re-names it.

Error message:

'Run-time error: '1004':'
'Copy method of worksheet class failed'

This error doesn't happen all the time???
If I close the workbook and re-open it seems to "reset" the worksheet
numbers it's creating and it will run for a while??? It looks like it gets
to a specific worksheet name and doesn't like it...

My macro selects the sheet "Booking Form" and putting a copy of it at the
end of the worksheets that have already created (MBA Wk1, MBA Wk2 etc).

I can't nominate which worksheet it goes next to as other worksheets are
only created if there is a certain value found in them...

The new worksheet is then re-named and the rest of the macro runs.

code:
Sheets("Booking Form").Copy After:=Sheets(Sheets.Count) error happens
here
ActiveSheet.Name = "MBA" & " " & Sheets("Sheet2").Range("A2").Value

Thanks in advance for your help
Regards
BeSmart