autofilter copy to new range
below is the new code i use o autofilter a column in a spreadsheet, then I
want to take the rows associated with that value and move them to a new area within the spreadsheet. However, it is only taking the cell that is autofiltered. How can I get the row to copy. Thanks Dim rng As Range DefVars Range("A199:A222").Select Selection.AutoFilter Field:=1, Criteria1:=Sheets(gstrCMCMPrice).Range("B23") Set rng = ActiveSheet.AutoFilter.Range If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Range("B5") Else MsgBox ("No visible data") End If |
autofilter copy to new range
Hi Monique,
How can I get the row to copy. Presumably, you do not mean the entire row (all 256 columns) because an entire row cannot be copied to B5, your destination cell. So what defines the row(s)? --- Regards, Norman "Monique" wrote in message ... below is the new code i use o autofilter a column in a spreadsheet, then I want to take the rows associated with that value and move them to a new area within the spreadsheet. However, it is only taking the cell that is autofiltered. How can I get the row to copy. Thanks Dim rng As Range DefVars Range("A199:A222").Select Selection.AutoFilter Field:=1, Criteria1:=Sheets(gstrCMCMPrice).Range("B23") Set rng = ActiveSheet.AutoFilter.Range If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Range("B5") Else MsgBox ("No visible data") End If |
autofilter copy to new range
I would like to have the filtered result copied to row 5. I just want that
result to move all fields associated with it somewhere else. "Norman Jones" wrote: Hi Monique, How can I get the row to copy. Presumably, you do not mean the entire row (all 256 columns) because an entire row cannot be copied to B5, your destination cell. So what defines the row(s)? --- Regards, Norman "Monique" wrote in message ... below is the new code i use o autofilter a column in a spreadsheet, then I want to take the rows associated with that value and move them to a new area within the spreadsheet. However, it is only taking the cell that is autofiltered. How can I get the row to copy. Thanks Dim rng As Range DefVars Range("A199:A222").Select Selection.AutoFilter Field:=1, Criteria1:=Sheets(gstrCMCMPrice).Range("B23") Set rng = ActiveSheet.AutoFilter.Range If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Range("B5") Else MsgBox ("No visible data") End If |
autofilter copy to new range
Hi,
I think it is because, you apply the filter utility only on column A then filter on first column (here, A). Instead, apply the filter utility on the whole range of data (several column), then filter on first column (A): ie, instead of Range("A199:A222").Select use Range("A199:F222").Select if F is the last column of data OR you could resize from the visible cells: instead of Resize(rng.Rows.Count - 1) use Resize(rng.Rows.Count - 1, number_of_columns_to_copy) -- Regards, Sébastien "Monique" wrote: I would like to have the filtered result copied to row 5. I just want that result to move all fields associated with it somewhere else. "Norman Jones" wrote: Hi Monique, How can I get the row to copy. Presumably, you do not mean the entire row (all 256 columns) because an entire row cannot be copied to B5, your destination cell. So what defines the row(s)? --- Regards, Norman "Monique" wrote in message ... below is the new code i use o autofilter a column in a spreadsheet, then I want to take the rows associated with that value and move them to a new area within the spreadsheet. However, it is only taking the cell that is autofiltered. How can I get the row to copy. Thanks Dim rng As Range DefVars Range("A199:A222").Select Selection.AutoFilter Field:=1, Criteria1:=Sheets(gstrCMCMPrice).Range("B23") Set rng = ActiveSheet.AutoFilter.Range If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Range("B5") Else MsgBox ("No visible data") End If |
All times are GMT +1. The time now is 05:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com