locked
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-KSGZ Wednesday, September 26, 2018 1:56 AM
    Monday, September 24, 2018 11:35 AM

All replies

  • Hi,

    Ask in the following forum.

    https://social.technet.microsoft.com/Forums/scriptcenter/en-us/home?forum=ITCG

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 25, 2018 1:13 AM
  • Thanks Alex, appreciated :)
    Tuesday, September 25, 2018 8:52 AM