Filter then paste
 

I need to create a Macro that will work with named worksheets that works
with an that is lloks like this AutoFilter Field:=24, Criteria1:="0",
Operator:=xlAnd

I need the macro to select all the data that meets the above Criteria and
paste it into a sheet named NetPILIQ . The NetPILIQ Sheet dosesnt start until
row 6. after the data has been pasted i need a total placed at the bottom.

Please Help Im now where close with this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Filter then paste
 

I forgot to includ that i only need theses columns X, , V, AD, B, E.

--
Message posted via http://www.officekb.com

Filter then paste
 

Sub copydata()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range
With Worksheets("sheetname")
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

--
Regards,
Tom Ogilvy

"jln via OfficeKB.com" wrote:

I need to create a Macro that will work with named worksheets that works
with an that is lloks like this AutoFilter Field:=24, Criteria1:="0",
Operator:=xlAnd

I need the macro to select all the data that meets the above Criteria and
paste it into a sheet named NetPILIQ . The NetPILIQ Sheet dosesnt start until
row 6. after the data has been pasted i need a total placed at the bottom.

Please Help Im now where close with this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Filter then paste
 

When I run this i get a Autofilter Method of range class failed.

Tom Ogilvy wrote:
Sub copydata()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range
With Worksheets("sheetname")
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

I need to create a Macro that will work with named worksheets that works
with an that is lloks like this AutoFilter Field:=24, Criteria1:="0",
[quoted text clipped - 5 lines]

Please Help Im now where close with this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Filter then paste
 

In contrast, it worked fine for me before I posted it.

Perhaps my assumptions on the location of the data do not match what is on
the sheet.

Use it as an example and craft to suit your needs.



--
regards,
Tom Ogilvy


"jln via OfficeKB.com" <u25956@uwe wrote in message
news:6623da8721795@uwe...
When I run this i get a Autofilter Method of range class failed.

Tom Ogilvy wrote:
Sub copydata()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range
With Worksheets("sheetname")
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

I need to create a Macro that will work with named worksheets that
works
with an that is lloks like this AutoFilter Field:=24, Criteria1:="0",
[quoted text clipped - 5 lines]

Please Help Im now where close with this.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1