how to correct an error in vba, and vlookup then copy paste the result

higam saiful

hi guys im a newbie in macro excel,im trying to copy the value from some workbook to another workbook and now im getting stuck with error in my code and i cannot find the solution im already do some research on the internet to find the solution for the error task but ended up with having no solution. the error is in "if" syntax I don't understand because I guess I already write the syntax correctly, but the error always appear there.

so here is my vba code.

Sub copy()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsCopyT As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim a As Long
Dim b As Long



    Set wsCopy = Workbooks("DNWID0162019011613-DOWID190115AM.xls").Worksheets("DNOZA0012015110201")
    Set wsDest = Workbooks("macro template.xlsm").Worksheets("Sheet1")
    Set wsCopyT = Workbooks("macro template.xlsm").Worksheets("Template")
    Set wsDestI = Workbooks("macro template.xlsm").Worksheets("Stock on Hand")

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row

    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row


    a = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
    b = wsDestI.Cells(wsCopy.Rows.Count, "G").End(xlUp).Row

For i = 1 To (a)

If wsCopy.Cells("E" & 9 + i).Value = "" Then
    For s = 4 To b
        If wsCopy.Cells("B" & 9 + i).Value = wsDestI.Cells("G" & s).Value Then
            wsDestI.Cells("G" & s).copy
                wsDest.Cells("S" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
            wsDestI.Cells("C" & s).copy
                wsDest.Cells("M" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
            wsDestI.Cells("M" & s).copy
                wsDest.Cells("P" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
        End If
    Next s
Else: wsCopy.Cells("E" & 9 + i).copy
        wsDest.Cells("M" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
    wsCopy.Cells("K" & 9 + i).copy
        wsDest.Cells("P" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
End If
Next i
wsDest.Range("M2:M" & lDestLastRow).Select
Dim max_baris As Long

max_baris = wsDest.Range("M2").End(xlDown).Row

For d = 2 To (max_baris)
    wsCopy.Cells("E2").copy
        wsDest.Cells("B" & d).PasteSpecial Paste:=xlPasteValues
    wsCopy.Cells("E3").copy
        wsDest.Cells("AB" & d).PasteSpecial Paste:=xlPasteValues
    wsCopy.Cells("E6").copy
        wsDest.Cells("AC" & d).PasteSpecial Paste:=xlPasteValues

    'wsDest.Range("L" & i + 1) = i
    If wsCopy.Range("B" & 8 + d) = ("") Then
        wsDest.Range("S" & d) = ("0")
        Else
        wsCopy.Range("B" & 9 + d).copy
            wsDest.Range("S" & d).PasteSpecial Paste:=xlPasteValues
        End If
    wsCopyT.Cells("A2").copy wsDest.Cells("A" & d)
    wsCopyT.Cells("C2").copy wsDest.Cells("C" & d)
    wsCopyT.Cells("J2").copy wsDest.Cells("J" & d)
    wsCopyT.Cells("J2").copy wsDest.Cells("K" & d)
    wsCopyT.Cells("Q2").copy wsDest.Cells("Q" & d)
    wsCopyT.Cells("R2").copy wsDest.Cells("R" & d)
    wsCopyT.Cells("AE2").copy wsDest.Cells("AE" & d)
    wsCopyT.Cells("AG2").copy wsDest.Cells("AG" & d)
    wsCopyT.Cells("AI2").copy wsDest.Cells("AI" & d)

    Next d

End Sub

any suggest ? thank you

Error 1004

If the error appears in line:

If wsCopy.Range("B" & 8 + d) = ("")

You can replace it with:

  • If IsEmpty(wsCopy.Range("B" & 8 + d).Value) = True or
  • If wsCopy.Range("B" & 8 + d).Value = ""

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related