Excel and VB script for a macro

Question

Hello Everyone,

If possible, I would need some support with this script.

Basically it works fine, it select the rows where I Have "IN PROGRESS". It copies all the columns of the rows though. I would like to copy only certain columns (e.g, Column B, C and F) of the rows containing the wording "IN PROGRESS".

Is there anyone so kind to show me how to amend the script?

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

Answer 1

If (StatusValue = strTest or StatusValue = strTest2) AND (wsSource.Range("D" & I ).value <> "" )then 

This will handle the most basic situations- but if you end up with any error values in column D (#NA, etc) then your code will error out

Answer 2

at the top of the macro, you have:

Const strTest = "IN PROGRESS"

add another similar line right after that one, we'll make this one strTest2:

Const strTest2= "ON HOLD"

Then in the code, the line that checks for this string:

If Not (rngCells.Find(strTest) Is Nothing) Then

change to

If Not (rngCells.Find(strTest) Is Nothing) OR Not (rngCells.Find(strTest2) Is Nothing) Then

Honestly, that logic makes my brain hurt, and the code suggests you are checking a bunch of columns (F through T) to find the status, instead of just the one you show (column G?).

If you have data validation on column G (and the status is ONLY in column G) and know that the entry will always be exactly (case sensitive) "IN PROGRESS" or "ON HOLD" with no extra spaces or other characters, I'd probably just use

For I = 1 To NoRows

        StatusValue= wsSource.Range("G" & I ).value

        If StatusValue = strTest or StatusValue = strTest2 then 

            '(the lines that move just your target columns goes here)

            DestNoRows = DestNoRows + 1

        End If

    Next I

Answer 3

Here is a picture too, hope it helps

Answer 4

replacing the A, B, C on the right side with the correct source columns, as those were not specified in your post

This is just aircode, tweak as needed

 KERATL, thanks for getting back to me.

I should have provided more info at first place, especially considering my almost non existent expertise in VB. :(

Please find attached a clear example of what I would like to get from the script. It's an example, not my real data.

You can find the excel file here

https://files.fm/u/xv892rt4

Answer 5

Replace

            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)

With something like

wsDest.Range("B" & DestNoRows) = wsSource.Range("A" & I)

wsDest.Range("C" & DestNoRows) = wsSource.Range("B" & I)

wsDest.Range("F" & DestNoRows) = wsSource.Range("C" & I)

replacing the A, B, C on the right side with the correct source columns, as those were not specified in your post

This is just aircode, tweak as needed