Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error (Copy Method of Worksheet class failed)
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" wrote in message oups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error (Copy Method of Worksheet class failed)
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" wrote in message oups.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error (Copy Method of Worksheet class failed)
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 Befo=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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error (Copy Method of Worksheet class failed)
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" wrote in message oups.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 Befo=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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error (Copy Method of Worksheet class failed)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 Copy method of worksheet class failed | Excel Discussion (Misc queries) | |||
Run Time Error 1004 Copy method of Worksheet class failed | Excel Programming | |||
Run-Time Error 1004 Copy method of worksheet class failed | Excel Programming | |||
Run Time error 1004 Paste Method of Worksheet Class Failed | Excel Programming | |||
HELP!! Excel 2000 Copy of worksheet class failed run time Error 1004 | Excel Programming |