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

    Pertanyaan

  • 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

    

    

    

    24 September 2018 11:35

Semua Balasan