VBA Excel Copy Range from a already autofiltered Range

0
votes

I have a worksheet containing data. As soon as something changes in a specific column, I want to copy the values of one column in this sheet to another worksheet, but only rows which match some criteria. So I have auto-filtered a range. This works. It only returns rows matching the filter. But from this filtered range, I only need one column. Somehow I cannot get this to work.

So my question would be, how can I only copy a specific column from a filtered range?

Code (snipped) I have so far:

Me.AutoFilterMode = False
With Me.Range("C4:D103")
    .AutoFilter Field:=2, Criteria1:="=Marge Only", Operator:=xlOr, Criteria2:="=Contracting"
    .SpecialCells(xlCellTypeVisible).Copy Destination:=ThisWorkbook.Worksheets("Result").Range("B5:B104")
End With

ThisWorkbook.SortResult

On Error Resume Next
    Me.AutoFilterMode = False
    Me.ShowAllData
On Error GoTo 0

The .SpecialCells(xlCellTypeVisible).Copy part copies too much data to the destination worksheet. I need something like: 

.Range("A:A").SpecialCells(xlCellTypeVisible).Copy

With .Range("A:A") my thought would be that only column A from the already filtered range would be copied. But this doesn't work.

So what would be your advice how to accomplish this?

2
vbaexcel

2 Answers

1
votes

You can modify your code slightly to copy only the column you need. This code assumes column A (but you can adjust) and it assumes row 4 is header data (you can also adjust.

With Me
    .Range("C4:D103").AutoFilter Field:=2, Criteria1:="=Marge Only", Operator:=xlOr, Criteria2:="=Contracting"
    .Range("A5:A103").SpecialCells(xlCellTypeVisible).Copy Destination:=ThisWorkbook.Worksheets("Result").Range("B5")
End With
0
votes

Is this what you are talking about? It checks column "I" for the criterial then it finds the first and last cells in a filter "A" column and copies the values between the two and paste it in column "O"

Sub copyColumn()
Dim StrRow As Long
Dim str As String
Dim str2 As String
Dim str3 As String
 With Sheet1
  .AutoFilterMode = False
   With .Range("A1:M1")
    .AutoFilter
    .AutoFilter Field:=9, Criteria1:="dog"
    StrRow = Sheets("Sheet1").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    str = .Range("A" & StrRow).Address
    str2 = .Range("A1").End(xlDown).Address
    .Range("O2:O" & str3).Value = .Range(str, str2).Value
    End With
    End With
End Sub