Run Tme Error '1004' Delete Method of Range Class Failed

Laybenet

New Member
Joined
Apr 2, 2011
Messages
8
Hi Asking,

File I created the same Macro in Excel 2010 and it works fine but when I try to recreate it with Excel 2003, I get the following error:

Run Tme Error '1004'
Delete Method of Range Class Failed.

Here is the code:

Sub Transfers()
Application.ScreenUpdating = False
Sheets("Request").Select
Range("Req").AutoFilter Field:=32, Criteria1:="Approved"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
If Range("Z1") = 0 Then
Selection.AutoFilter
Range("A5").Select
Else
Range("=OFFSET(Request!$A$4,1,0,COUNTA(OFFSET(Request!$A$4,1,0,9999)),34)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("In Progress").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Request").Select
If Range("Z1") > 0 Then
Range("=OFFSET(Request!$A$4,1,0,COUNTA(OFFSET(Request!$A$4,1,0,9999)),32)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
Selection.AutoFilter
Range("A5").Select
End If
'This section you need to input your criteria for shifting to sheet Processed.
'Change the AutoFilter field if criteria is in different column to column 'R' (18)
Sheets("In Progress").Select
Range("Prog").AutoFilter Field:=33, Criteria1:="Completed"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
If Range("Z1") = 0 Then
Selection.AutoFilter
Range("A5").Select
Else
Range("=OFFSET('In Progress'!$A$4,1,0,COUNTA(OFFSET('In Progress'!$A$4,1,0,9999)),34)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Processed").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A5").Select
Sheets("In Progress").Select
Range("Z1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[4]C[-25]:R[9999]C[-25])"
If Range("Z1") > 0 Then
Range("=OFFSET('In Progress'!$A$4,1,0,COUNTA(OFFSET('In Progress'!$A$4,1,0,9999)),34)").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Rows.Delete
End If
End If
ActiveSheet.AutoFilterMode = False
Range("A5").Select
Sheets("Request").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Any Suggestion?
<!-- / message -->
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top