CODEWITHSUNDEEP
excelvba
ASH
ASH

Reputation: 20302

How can I copy only specific cells

I tested a small script that looks for the string 'MISSING' and copies/pastes data to another sheet.

Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Impact Analysis")
LastRow = sht.Cells(sht.Rows.count, "A").End(xlUp).Row
Set Source = ActiveWorkbook.Worksheets("Impact Analysis")
Set Target = ActiveWorkbook.Worksheets("MISSING")

    j = 11     ' Start copying to row 11 in target sheet
    For Each c In Source.Range("O11:O" & LastRow)
        If c = "MISSING" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c

However, instead of coping the entire row, I would like to copy the following columns, with the same corresponding rows, in this order: J, F, E, A, and I. So, from the 'Impact Analysis' sheet, if Column O has the word 'MISSING', I want to copy J, F, E, A, and I as well as the data in Column O, and paste all of this into the sheet named 'MISSING'. How can I modify the code to do that?

Upvotes: 1

Views: 78

Answers (1)

L42
L42

Reputation: 19727

Using your current code, you can try below:

For Each c In Source.Range("O11:O" & LastRow)
    If UCase(c.Value2) = "MISSING" Then ' non case sensitive search
        c.Offset(, -5).Copy Target.Range("A" & j) 'copy J
        c.Offset(, -9).Copy Target.Range("B" & j) 'copy F
        c.Offset(, -10).Copy Target.Range("C" & j) 'copy E
        c.Offset(, -14).Copy Target.Range("D" & j) 'copy A
        c.Offset(, -6).Copy Target.Range("E" & j) 'copy I
        c.Copy Target.Range("F" & j) 'copy O

        j = j + 1
    End If
Next

Above copies values from columns in the order you specify and puts it on columns A to F of the target sheet.

Upvotes: 1

Related Questions