Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter, Copy and Paste | Excel Discussion (Misc queries) | |||
Filter, Cut and Paste Problem | Excel Discussion (Misc queries) | |||
Copy Paste in Filter | Setting up and Configuration of Excel | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming |