Error when copying worksheets

[BeSmart]

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

 

[Sheeloo]

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]

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 are:

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.

 

[Sheeloo]

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

 

[Sheeloo]

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

 

[BeSmart]

The error still happened with both your suggestions?

I ran your "copysheet" code and it broke on the rename sheet row as expected
and didn't create the Book Form (2) worksheet.

I only have 4 sheets in the workbook so that can't be it...

I replaced the activesheet line with yours and it still gave me the error???

I've changed the code
from: Sheets("Booking Form").Copy After:=Sheets(Sheets.Count)
to: Sheets("Booking Form").Copy After:=Sheets(2)
and it's working ok at the moment - but I've got a feeling something else
I'm doing is triggering the error.

Here's the full macro's code incase you can see that I've done something
else wrong. Thanks heaps for trying to help me - I'm sure it's a stupid user
problem:

The "Booking Form" sheet it copies from has advanced filter rows on it
The "Booking Form" is formatted via a macro run before this one, then if
there week 1's total value is >0 then it calls "Week1()" sub to run.

____________________________________
Sub Week1()

Dim rngCheck As Range, rngC As Range
Set r = ActiveSheet.Range("A19:A148")
Set rngCheck = Sheets("Data").Range("C101")
nLastRow = r.Rows.Count + r.Row - 1

Application.ScreenUpdating = False
Sheets("Data").Visible = True

For Each rngC In rngCheck
If rngC.Value > 0 Then

Sheets("Booking Form").Copy After:=Sheets(2)
ActiveSheet.Name = "MBA" & " " & Sheets("Data").Range("A2").Value
Range("B18").FormulaR1C1 = "=""=""&Data!R[-16]C[-1]"
ActiveSheet.Range("A19:K148").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B17:B18"), Unique:=False
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
Range("E18").ClearContents
ActiveSheet.ShowAllData
ActiveSheet.Shapes("planned").Delete
ActiveSheet.Shapes("week_list").Delete
Range("G11").FormulaR1C1 = "=VLOOKUP(""
""&MID(R[7]C[-5],3,10),WEEKS,2,FALSE)"
Range("A1").Select

Else
'>> Call Week2 (the next macro to automatically run for the next week)
End If
Next
Sheets("Data").Visible = False
Application.ScreenUpdating = True
'>> Call Week2

End Sub

 

[Sheeloo]

Apparently you did not notice my last post...

Anyway to test change your line for renaming the new sheet to
Sheets(Sheets.Count).Name = "MBA" & " " & Sheets("Sheet2").Range("A2").Value

You may also put a
MsgBox activesheet.Name before the copy command to see which one is the
active sheet when the code fails.

If possible send the file to me
add @hotmail.com to to_sheeloo to get my email id

The error still happened with both your suggestions?

I ran your "copysheet" code and it broke on the rename sheet row as expected
and didn't create the Book Form (2) worksheet.

I only have 4 sheets in the workbook so that can't be it...

I replaced the activesheet line with yours and it still gave me the error???

I've changed the code
from: Sheets("Booking Form").Copy After:=Sheets(Sheets.Count)
to: Sheets("Booking Form").Copy After:=Sheets(2)
and it's working ok at the moment - but I've got a feeling something else
I'm doing is triggering the error.

Here's the full macro's code incase you can see that I've done something
else wrong. Thanks heaps for trying to help me - I'm sure it's a stupid user
problem:

The "Booking Form" sheet it copies from has advanced filter rows on it
The "Booking Form" is formatted via a macro run before this one, then if
there week 1's total value is >0 then it calls "Week1()" sub to run.

____________________________________
Sub Week1()

Dim rngCheck As Range, rngC As Range
Set r = ActiveSheet.Range("A19:A148")
Set rngCheck = Sheets("Data").Range("C101")
nLastRow = r.Rows.Count + r.Row - 1

Application.ScreenUpdating = False
Sheets("Data").Visible = True

For Each rngC In rngCheck
If rngC.Value > 0 Then

Sheets("Booking Form").Copy After:=Sheets(2)
ActiveSheet.Name = "MBA" & " " & Sheets("Data").Range("A2").Value
Range("B18").FormulaR1C1 = "=""=""&Data!R[-16]C[-1]"
ActiveSheet.Range("A19:K148").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("B17:B18"), Unique:=False
For n = nLastRow To 1 Step -1
If Cells(n, "A").EntireRow.Hidden = True Then
Rows(n).Delete
End If
Next
Range("E18").ClearContents
ActiveSheet.ShowAllData
ActiveSheet.Shapes("planned").Delete
ActiveSheet.Shapes("week_list").Delete
Range("G11").FormulaR1C1 = "=VLOOKUP(""
""&MID(R[7]C[-5],3,10),WEEKS,2,FALSE)"
Range("A1").Select

Else
'>> Call Week2 (the next macro to automatically run for the next week)
End If
Next
Sheets("Data").Visible = False
Application.ScreenUpdating = True
'>> Call Week2

End Sub

--
Thank for your help
BeSmart

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