macro: copy paste cell if condition met

Question

There’s one step that’s stuck, to update the stock number (column "D") in the database_ gudang (stock in the database_ gudang is added to the amount of receipt (column "K") from form_penerimaan)

The update is based on the name of the item (nama barang), so if the name of the item (column "C") in the form_penerimaan is the same as the name of the item (column "B") in the database_ gudang, the stock in database_ gudang will be updated.

but there’s a problem, which is updated only in rows 2,9,10 (yellow cell). A row of 3,4,5 should also be updated.

Thank you very much for your help.

Regards.

Sub Module1()
    s = 10
    OT1 = Sheets("Database_Gudang").Cells(Rows.Count, "D").End(xlUp).Row

    For j = 2 To OT1
        NB1 = Sheets("Database_Gudang").Cells(j, "B").Value

        Sheets("Form_Penerimaan").Activate
        If Cells(s, "C").Value = NB1 And Cells(s, "C").Value <> "" Then
            Sheets("Form_Penerimaan").Cells(s, "Q").Copy

            Sheets("Database_Gudang").Activate
            Sheets("Database_Gudang").Cells(j, "G").Select
            Selection.PasteSpecial Paste:=xlPasteValues

            s = s + 1
        End If
    Next j
End Sub

Answer 1

Hi and Welcome to stackoverflow :)

Avoid the use of .Select and .Activate. Directly work with variables and objects. You may want to see How to avoid using Select in Excel VBA

You are facing that issue because you are not looping through the cells of the 2nd sheet.

Is this what you are trying? (UNTESTED)

I have commented the code so you may not have a problem in understanding it. If you do then share the exact error message and we will take it from there.

Sub Sample()
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim i As Long, j As Long
    Dim wsThisLRow As Long, wsThatLRow As Long

    '~~> Set your worksheets
    Set wsThis = ThisWorkbook.Sheets("Database_Gudang")
    Set wsThat = ThisWorkbook.Sheets("Form_Penerimaan")

    '~~> Find relevant last row in both sheets
    wsThisLRow = wsThis.Range("D" & wsThis.Rows.Count).End(xlUp).Row
    wsThatLRow = wsThat.Range("C" & wsThat.Rows.Count).End(xlUp).Row

    With wsThis
        '~~> Loop through cell in Database_Gudang
        For i = 2 To wsThisLRow
            '~~> Loop through cell in Form_Penerimaan
            For j = 10 To wsThatLRow
                '~~> Compare values and get values across if applicable
                If .Range("B" & i).Value = wsThat.Range("C" & j).Value Then
                    .Range("G" & i).Value = wsThat.Range("Q" & j).Value
                    Exit For
                End If
            Next j
        Next i
    End With
End Sub