Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with my cut and paste code
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200609/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with my cut and paste code
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 -- Regards, Tom Ogilvy "jln via OfficeKB.com" wrote: 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 -- 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 | |||
Got the Copy/Cut Code But what is the Paste Code | Excel Programming | |||
Help!! code to paste | Excel Programming | |||
paste code | Excel Programming | |||
Paste Code | Excel Programming | |||
Where do I paste this Code? | Excel Programming |