locked
VBA Code to Filter Data, Copy and Paste in New Sheet RRS feed

  • Question

  • I have a spreadsheet with 2 columns: Status and Actions. The status can be 0, 5 or 20 and the Actions can be any number. I want to filter the Status' for all with 20, then copy and paste the corresponding rows (with the actions) into a new sheet. 
    • Moved by Mark Liu-lxf Wednesday, October 24, 2012 5:39 AM (From:Visual Basic General)
    Tuesday, October 23, 2012 5:50 PM

Answers

  • Hi Werthj24,

    Thank you for posting in the MSDN Forum.

    Suppose sheet left in the below picture is what you have and sheet right is what you want.

    The below macro might work for you.

    Sub test()
        
        'Suppose you want to filter data in ColumnA & Column B of Sheet1.
        Range("A1").EntireRow.Insert
        
        'Find the LastRow
        Dim LastLine As Long
        LastLine = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
        
        'Add filter
        Range("A:B").AutoFilter Field:=2, Criteria1:=20
        
        'Copy the filtered data.
        Range("A2:B" & LastLine).SpecialCells(xlCellTypeVisible).Copy
        
        'Paste to Sheet2
        Sheets(2).Range("A1").PasteSpecial
        
        'Delete the first row as well as the filter
        Range("A1").EntireRow.Delete
        
    End Sub

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Quist Zhang Wednesday, October 24, 2012 6:23 AM
    • Marked as answer by Quist Zhang Thursday, November 1, 2012 6:45 AM
    Wednesday, October 24, 2012 6:14 AM

All replies

  • This is the forum for VB Net, not for VBA.

    Two doors down to the left. ;-)


    Please call me Frank :)

    Tuesday, October 23, 2012 5:56 PM
  • Hi Werthj24,

    Thank you for posting in the MSDN Forum.

    Suppose sheet left in the below picture is what you have and sheet right is what you want.

    The below macro might work for you.

    Sub test()
        
        'Suppose you want to filter data in ColumnA & Column B of Sheet1.
        Range("A1").EntireRow.Insert
        
        'Find the LastRow
        Dim LastLine As Long
        LastLine = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
        
        'Add filter
        Range("A:B").AutoFilter Field:=2, Criteria1:=20
        
        'Copy the filtered data.
        Range("A2:B" & LastLine).SpecialCells(xlCellTypeVisible).Copy
        
        'Paste to Sheet2
        Sheets(2).Range("A1").PasteSpecial
        
        'Delete the first row as well as the filter
        Range("A1").EntireRow.Delete
        
    End Sub

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Quist Zhang Wednesday, October 24, 2012 6:23 AM
    • Marked as answer by Quist Zhang Thursday, November 1, 2012 6:45 AM
    Wednesday, October 24, 2012 6:14 AM
  • Hello,

    Maybe you would like to use my UDF vlookupall:

    http://sulprobil.com/html/lookup-variants.html

    Regards,

    Bernd


    Reverse("moc.LiborPlus.www")

    Wednesday, October 24, 2012 5:09 PM