Create a script to filter and copy some data in Excel to a new Sheet of the same Workbook RRS feed

  • Question

  • Hello everyone!

    I would need some support to create a VB script able to filter in EXCEL some data based on the content of a Column (for ex Column H, where I have a List of possible values with Data validation.

     I would need to have selected and copied only rows where I have these values: "In Progress" AND "ON Hold".

    Selected these rows, the script should be able to copy only certain columns of these rows. Columns B,C,F,H.
    Ideally the script should overwrite previous data extracted activating the macro.

    I have a script doing the first part, selecting and copying all the rows. Unfortunately I would need to copy only certain columns.

    Hopefully what I wrote makes sense :(

    Any support appreciated!

    Thank you!

    Sub BankMove()
    Const strTest = "IN PROGRESS"
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim NoRows As Long
    Dim DestNoRows As Long
    Dim I As Long
    Dim rngCells As Range
    Dim rngFind As Range
        Set wsSource = ActiveSheet
        NoRows = wsSource.Range("A65536").End(xlUp).Row
        DestNoRows = 1
        Set wsDest = ActiveWorkbook.Worksheets.Add
        For I = 1 To NoRows
            Set rngCells = wsSource.Range("T" & I & ":F" & I)
            If Not (rngCells.Find(strTest) Is Nothing) Then
                rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
                DestNoRows = DestNoRows + 1
            End If
        Next I
    End Sub




    • Moved by Alex Li-MSFT Wednesday, September 26, 2018 1:56 AM
    Monday, September 24, 2018 11:35 AM

All replies