Copy paste into filtered rows

[acurious1982]

Hi,

consider column A has a number of rows which we want to filter for certain values. After that we want to copy paste those cells in the filtered A range into its adjacent column (i.e. B). If you simply copy paste them into B, the copied values will be pasted into the unfiltered rows of A instead of against each A range value!!!

I hope I made sense here. please see below as eg:

Column A Column B
15
20
35
48
55

Filiter A for anything above 20, that is:

Column A Column B
35
48
55

thanks heaps

 

[dmt32]

Hi,
Others here may have better solutions but one way would be to copy the values to Column B before you apply the filter.
see if this helps. Place code in standard module.

Sub CopyFilterData()
    Dim item As Range
    Dim rng As Range
    Dim FilterValue As Integer
    FilterValue = 20
    With Worksheets(1)
        Set rng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    rng.AutoFilter
    For Each item In rng
        If item.Value > FilterValue Then item.Offset(0, 1).Value = item.Value
    Next
    rng.AutoFilter Field:=1, Criteria1:=">" & FilterValue
End Sub

I have assumed your data is located on sheet 1 - change as required.