Loop to match & paste to another sheet

Question

I have two sheets in one workbook. One sheet ("Data") has data. The data is the Name (column C), and 4 columns (D:I) of questions with answers that match the name across the row.

I am trying to copy the answers on the Data sheet for column D if the name in Data column C matches to a name listed in the Presentation sheet.

For column D:

-If name in sheet "Data" column C cell matches sheet "Presentation" A1 cell

-Paste data from sheet "Data" column D cell j to sheet "Presentation" column A cell k where j and k are integer counters that count the rows

Nothing is happening when I execute this code and I really don't understand why.

Sub TestLogic()

Dim colD As Range
Set colD = Worksheets("Data").Range("D3:D23344")

Dim j As Integer
'j is row counter on Data
j = 3
Dim k As Integer
'k is the row counter on Presentation
k = 4

'Iterate through all cells in Column D
For Each Cell In colD

    'If Drop down on Presentation is equal to Center name on Data
    If Worksheets("Presentation").Cells(1, "A").Value = Worksheets("Data").Cells(j, "C").Value Then

        'Copy all matching cells to Presentation
            Worksheets("Data").Cells(j, "D") = Worksheets("Presentation").Cells(k, "A")

        End If
        j = j + 1
        k = k + 1
    Next Cell

End Sub

You've got the order mixed up: Worksheets("Presentation").Cells(k, "A").Value = Worksheets("Data").Cells(j, "D").Value. — BigBen
Don't use Integer, use Long. See stackoverflow.com/questions/26409117/… — BigBen
Thanks Big Ben. I originally had these as Long data types, and had the code for the copy/paste as you have it listed, but didn't get anything printed. Any other advice that may help? — K-Pandora

Blake Daniel Blake Daniel · Accepted Answer · 2020-02-19T21:58:12

I agree with the answer @BigBen gave. Also, you have k = k + 1 outside the for loop which will create gaps every time the if then statement is not met. I reworked a little of the code to help:

Dim rc As Long, ws_Data As Worksheet, ws_Pres As Worksheet, k As Long

Set ws_Data = ActiveWorkbook.Worksheets("Data")
Set ws_Pres = ActiveWorkbook.Worksheets("Presentation")

rc = ws_Data.Range("D" & Rows.count).End(xlUp).Row
k = 4

'Iterate through all used cells
For i = 3 To rc

    'If Drop down on Presentation is equal to Center name on Data
    If ws_Pres.Range("A1") = ws_Data.Range("C" & i) Then
        'Copy all matching cells to Presentation
        ws_Pres.Range("A" & k) = ws_Data.Range("D" & i)
        k = k + 1
    End If

Next

Loop to match & paste to another sheet

1 Answers