Excel VBA - copy only row-range that contains specific value in column on a different workbook

Question

I want to create a macro that copies a row-range if the cell in column DA contains a specific value.

For example:

If the column DA10 contains the value 3-incompletion, N10 contaings nothing, CI10 contains nothing - then the macro should copy the cells F10, H10 and DA10 into the workbook reportWB. I don't know how to do it.

Progression so far (thanks to Shai - I'm still to stupid to make it work):

Sub insertINCOMPLETION()

Dim dataWB As Workbook
Dim reportWB As Workbook
Dim workB As Workbook
Dim incomplRNG As Range
Dim LastRow6 As Long
Dim LastRow7 As Long

For Each workB In Application.Workbooks
    If Left(workB.Name, 5) = "15B2" Then
        Set dataWB = workB
        Exit For
    End If
Next

    If Not dataWB Is Nothing Then
        Set reportWB = ThisWorkbook

    With reportWB.Sheets("getDATA")
        LastRow6 = .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Row
    End With

With dataWB.Sheets("Data")
    LastRow7 = .Cells(.Rows.Count, "F").End(xlUp).Row

     If InStr(.Range("DA" & LastRow7).Value2, "3-Incompletion") > 0 And _
        Trim(.Range("N" & LastRow7).Value2) = "" And _
        Trim(.Range("CI" & LastRow7).Value2) = "" Then
        Set incomplRNG = Application.Union(.Range("F8:F" & LastRow7), .Range("H8:H" & LastRow7), .Range("DA8:DA" & LastRow7))
        incomplRNG.Copy
        reportWB.Sheets("getDATA").Range("B" & LastRow6).PasteSpecial xlPasteValues
    End If
End With
End If
End Sub

Still doesn't work. Ends after checking the column DA.

where is the code part where you compare the cell with "3-incompletion" ? You can use If InStr(.Cells(10, "AD"), "3-incompletion") > 0 Then — Shai Rado
I'm not really into the "if"-thing - tried to sort the data before and only copy the sorted area, but was to embarrassed to post it because it didn't work... I'll look if I can implement your suggestion. Also: thank you for editing all of my post. Every time I submit a question, I'm thinking that you don't have to edit it :-D - 2 mins later... — Bluesector

Shai Rado Shai Rado · Accepted Answer · 2017-12-07T09:20:16

You need to add the code part where you compare the cell's text with "3-incompletion". You can use InStr function to achieve this.

With dataWB.Sheets("Data")
    ' === not sure why you want to look for the last row ??? ===
    'LastRow7 = .Cells(.Rows.Count, "F").End(xlUp).Row

    ' compare the value in row 10 (as in your post example)
    LastRow7 = 10

     If InStr(.Range("AD" & LastRow7).Value2, "3-incompletion") > 0 And _
        Trim(.Range("AD" & LastRow7).Value2) = "" And _
        Trim(.Range("CI" & LastRow7).Value2) = "" Then
        Set incomplRNG = Application.Union(.Range("F8:F" & LastRow7), .Range("H8:H" & LastRow7), .Range("DA8:DA" & LastRow7))
        incomplRNG.Copy
        reportWB.Sheets("getDATA").Range("B" & LastRow6).PasteSpecial xlPasteValues
    End If          
End With

Excel VBA - copy only row-range that contains specific value in column on a different workbook

1 Answers