autofilter copy to new range

[Monique]

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

 

[Norman Jones]

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)?

 

[Monique]

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.

 

[sebastienm]

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)