Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter Cut/Copy not working with Name Range | Excel Worksheet Functions | |||
Insert Row/Copy with Autofilter | Excel Discussion (Misc queries) | |||
What is Range For Chart AutoFilter Range VBA? | Excel Discussion (Misc queries) | |||
AutoFilter and copy | Excel Programming | |||
Copy an autofilter range | Excel Programming |