| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto filter question
Hello,
What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
Try this
Dim rng As Range Set rng = Selection rng.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" rng.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet15").Select ActiveSheet.Paste rng.Delete Sheets("Sheet15").Name = "SOI" -- HTH RP (remove nothere from the email address if mailing direct) "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
Hi Lime
Try this http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
Thanks Bob & Ron. You both were a big help..
The below work well but, it remove the header from the main sheet. All I need is the filter results and leave the header. Any sugestion? "Bob Phillips" wrote: Try this Dim rng As Range Set rng = Selection rng.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" rng.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet15").Select ActiveSheet.Paste rng.Delete Sheets("Sheet15").Name = "SOI" -- HTH RP (remove nothere from the email address if mailing direct) "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
See the second example on my page
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _ .SpecialCells(xlCellTypeVisible) -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... Thanks Bob & Ron. You both were a big help.. The below work well but, it remove the header from the main sheet. All I need is the filter results and leave the header. Any sugestion? "Bob Phillips" wrote: Try this Dim rng As Range Set rng = Selection rng.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" rng.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet15").Select ActiveSheet.Paste rng.Delete Sheets("Sheet15").Name = "SOI" -- HTH RP (remove nothere from the email address if mailing direct) "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
I am placing Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
..Columns.Count) _ .SpecialCells(xlCellTypeVisible) but I keep bugging... "Ron de Bruin" wrote: Hi Lime Try this http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
Hi Lime
I think you copy it exact in Bob's example ? Try this Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim rng2 As Range Set rng = Selection DeleteValue = "STATE OF ILLINOIS" rng.AutoFilter Field:=41, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng.EntireRow.Copy Sheets("Sheet15").Range("A1") rng2.EntireRow.Delete End If End With ActiveSheet.AutoFilterMode = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... I am placing Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _ .SpecialCells(xlCellTypeVisible) but I keep bugging... "Ron de Bruin" wrote: Hi Lime Try this http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto filter question
Thanks.. Ron
It did not work as I wanted so I just copied the header back over. It seemed like a logical work around. I really do apreaciate your Help as always. Lime "Ron de Bruin" wrote: Hi Lime I think you copy it exact in Bob's example ? Try this Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range Dim rng2 As Range Set rng = Selection DeleteValue = "STATE OF ILLINOIS" rng.AutoFilter Field:=41, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng.EntireRow.Copy Sheets("Sheet15").Range("A1") rng2.EntireRow.Delete End If End With ActiveSheet.AutoFilterMode = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... I am placing Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _ .SpecialCells(xlCellTypeVisible) but I keep bugging... "Ron de Bruin" wrote: Hi Lime Try this http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Lime" wrote in message ... Hello, What I am trying to do is copy all the results of a filter and paste them to a new sheet, and then deleting the rows of filter results from the main sheet. I will have many different filters following this one so I am assuming NEXT. Here is what I have so far. But it only copies certain rows, I need it to copy all of the filter reusts as the results change daily. Any Help... Thanks, Lime Selection.AutoFilter Field:=41, Criteria1:="STATE OF ILLINOIS" Rows("18:18").Select Range("AJ18").Activate Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet15").Select ActiveSheet.Paste Sheets("Sheet15").Select Sheets("Sheet15").Name = "SOI" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto-filter Question | Excel Discussion (Misc queries) | |||
| Auto-filter question | Excel Discussion (Misc queries) | |||
| Auto Filter question | Excel Worksheet Functions | |||
| auto filter question | Excel Worksheet Functions | |||
| auto filter question | Excel Worksheet Functions | |||
Linear Mode
