Run time error (Copy Method of Worksheet class failed)

Hi

I have a macro that makes a copy of particular sheet in the file,
renames it and performs some calculations. The macro contains a loop
and thus it should continue to do this until the loop is completed.
The problem is that the macro stops in between (abruptly) with a run
time error (Copy Method of Worksheet class failed) and on debugging it
highlights the 'copy sheet code' in VBA. Once I close the VBA screen
and try to make copy of the particular sheet manually (using Mouse and
the ctrl key), excel fails to make a copy. I do not understand why this
happens. I than close this file and on reopening the macro works fine
until it encounter this error again.

Do you have any idea why this is happening

Thanks
Karthik Bhat

Two possible reasons are that you trying to remove the last worksheet or
trying to name a sheet with the same name, but you may wish to repost with
the code so people can see what te code is doing.

HTH
J

"Karthik Bhat - Bangalore" <karthik.bhat@db.com> wrote in message
news:1136263900.537065.309200@g43g2000cwa.googlegroups.com...
> Hi
>
> I have a macro that makes a copy of particular sheet in the file,
> renames it and performs some calculations. The macro contains a loop
> and thus it should continue to do this until the loop is completed.
> The problem is that the macro stops in between (abruptly) with a run
> time error (Copy Method of Worksheet class failed) and on debugging it
> highlights the 'copy sheet code' in VBA. Once I close the VBA screen
> and try to make copy of the particular sheet manually (using Mouse and
> the ctrl key), excel fails to make a copy. I do not understand why this
> happens. I than close this file and on reopening the macro works fine
> until it encounter this error again.
>
> Do you have any idea why this is happening
>
> Thanks
> Karthik Bhat
>

Show us the code? Is it trying to rename it to an existing worksheet?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Karthik Bhat - Bangalore" <karthik.bhat@db.com> wrote in message
news:1136263900.537065.309200@g43g2000cwa.googlegroups.com...
> Hi
>
> I have a macro that makes a copy of particular sheet in the file,
> renames it and performs some calculations. The macro contains a loop
> and thus it should continue to do this until the loop is completed.
> The problem is that the macro stops in between (abruptly) with a run
> time error (Copy Method of Worksheet class failed) and on debugging it
> highlights the 'copy sheet code' in VBA. Once I close the VBA screen
> and try to make copy of the particular sheet manually (using Mouse and
> the ctrl key), excel fails to make a copy. I do not understand why this
> happens. I than close this file and on reopening the macro works fine
> until it encounter this error again.
>
> Do you have any idea why this is happening
>
> Thanks
> Karthik Bhat
>

Hi

Thanks for your help ..
The code is not trying to rename it to an existing worksheet.......
And what bothers me is that after the error has occurred I am unable to
make a copy of any sheet within the workbook manually (using Mouse and/
or the ctrl key). And the code stops at different counter numbers every
time I try to rerun the code.

Here is the code

Sub Invoice_Creator()

With Sheets("Summary")
For i = 1 To .UsedRange.Rows.Count ' 25 to 50 in all
If .Cells(i, 1) <> "" Then
Country = Cells(i, 1)

' The error occures at the below statement
Sheets("Std Invoice").Copy Before:=Sheets(7)

Sheets("Std Invoice (2)").Name = Country
Sheets(Country).Select

Range("C1").FormulaR1C1 = Right(Country, 4)
Range("D1").FormulaR1C1 = "=RC[-1]*1"
Range("D1").Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D1").ClearContents

Range("A75").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Sheets("Invoice_Data").Select
Selection.AutoFilter Field:=25, Criteria1:=Right(Country, 4),
Operator:=xlAnd
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets(Country).Select
Range("A75").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J48").Select
Selection.Copy
Range("A1").Select
Sheets("Summary").Select
Cells(i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Else
End If
Sheets("Summary").Select
Range("A1").Select

Next i
End With

End Sub

At a quick guess Karthik, I would think it is due to not properly qualifying
ranges. For instance, this

With Sheets("Summary")
For i = 1 To .UsedRange.Rows.Count ' 25 to 50 in all
If .Cells(i, 1) <> "" Then
Country = Cells(i, 1)

should be

With Sheets("Summary")
For i = 1 To .UsedRange.Rows.Count ' 25 to 50 in all
If .Cells(i, 1) <> "" Then
Country = .Cells(i, 1)

so that it refers to the summary sheet not an active sheet.

Correct any others and see if that cures it first.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Karthik Bhat - Bangalore" <karthik.bhat@db.com> wrote in message
news:1136279594.673698.243740@g43g2000cwa.googlegroups.com...
> Hi
>
> Thanks for your help ..
> The code is not trying to rename it to an existing worksheet.......
> And what bothers me is that after the error has occurred I am unable to
> make a copy of any sheet within the workbook manually (using Mouse and/
> or the ctrl key). And the code stops at different counter numbers every
> time I try to rerun the code.
>
> Here is the code
>
> Sub Invoice_Creator()
>
> With Sheets("Summary")
> For i = 1 To .UsedRange.Rows.Count ' 25 to 50 in all
> If .Cells(i, 1) <> "" Then
> Country = Cells(i, 1)
>
> ' The error occures at the below statement
> Sheets("Std Invoice").Copy Before:=Sheets(7)
>
> Sheets("Std Invoice (2)").Name = Country
> Sheets(Country).Select
>
> Range("C1").FormulaR1C1 = Right(Country, 4)
> Range("D1").FormulaR1C1 = "=RC[-1]*1"
> Range("D1").Copy
> Range("D1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Selection.Copy
> Range("C1").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Range("D1").ClearContents
>
> Range("A75").Select
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.ClearContents
> Sheets("Invoice_Data").Select
> Selection.AutoFilter Field:=25, Criteria1:=Right(Country, 4),
> Operator:=xlAnd
> Range("A1").Select
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.SpecialCells(xlCellTypeVisible).Select
> Selection.Copy
> Sheets(Country).Select
> Range("A75").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Range("J48").Select
> Selection.Copy
> Range("A1").Select
> Sheets("Summary").Select
> Cells(i, 2).Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Else
> End If
> Sheets("Summary").Select
> Range("A1").Select
>
> Next i
> End With
>
> End Sub
>

I had the same kind of problem last November (see my post "Run-time
Error 1004" in public.excel on 22nd Nov, and follow the link that
Norman Jones suggested).

It would appear that Excel does not manage memory usage very well in
these circumstances. I had to amend what I was doing to keep track of
where I had got up to each time around my loops (by writing the loop
counter to a cell). Then when it crashed, I could restart Excel and the
macro would pick up from where it had crashed.

Hope this helps.

Pete