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
If the error appears in line:
If wsCopy.Range("B" & 8 + d) = ("")
You can replace it with:
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments