CODEWITHSUNDEEP
excelvba
roberto limongi
roberto limongi

Reputation: 3

Select the result of filter

I want to select the row shown as the result of a filter then paste it into an other worksheet of excel.

I tried to record the macro, but the problem is that the row selected are always the same (they are not updated from what the filter shows).

below the VBA code:

    Sub Macro6()
    '
    ' Macro6 Macro
    '

    '
    Range("B2").Select
    Selection.Copy
    Sheets("Foglio 1").Select
    ActiveSheet.Range("$A$1:$AB$31501").AutoFilter Field:=5, Criteria1:= _
      "IMMACOLATA"
    Range("F25190:J25194").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Foglio2").Select
    Range("A6").Select
    ActiveSheet.Paste
    End Sub

how can i fix the code?

thank you

Upvotes: 0

Views: 407

Answers (1)

yashika vaish
yashika vaish

Reputation: 201

You can't rely on static ranges, Try the below code :

Sub copyFilterData()

    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Set ws = ThisWorkbook.Sheets("Foglio 1")
    Set ws1 = ThisWorkbook.Sheets("Foglio2")
    ws.Range("$A$1:$AB$31501").AutoFilter Field:=5, Criteria1:= _
      "IMMACOLATA"
    ws.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    ws1.Cells(1, 1).PasteSpecial
end sub

Upvotes: 1

Related Questions