Help with my cut and paste code

[jln via OfficeKB.com]

IM trying to take and auto filter the Toepiexp page and the paste B, E,V, X,
AD on to NetPilq page and then place a total at the bottom.
Here is my code That TOM posted when i posed the question BUt im getting an
error Im getting an autofilter method of range class failed on this line of
code. rng.AutoFilter Field:=24, Criteria1:=">0" I have looked in the object
browser but the code looks right So i could really use some help.

Sub Copydata()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("TOEPIEXP")
Set rng = Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:=">0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If

End Sub

Also What do i need to add to my
code to do theses 2 things
1. Remove the filter after the macro is finished.
2. Clear the NetPILIQ worksheet of everything below row 6

 

[Tom Ogilvy]

There did appear to be a period missing on one line - I don't know if that
was your omission, mine, or an email problem. anyway, this worked for me: (
I assume you want the area cleared before copying the data - wouldn't make
sense otherwise).

Sub Copydata()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
With Worksheets("NetPILIQ")
.Range("A7:IV65536").Clear
End With
With Worksheets("TOEPIEXP")
..AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:=">0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,V:V,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
..Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A6")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
End Sub