BrutalDawg
03-13-2017, 08:58 AM
Hello Board,
I have a macro currently setup to find the change in an order I receive. Where sheet1 is previous week, sheet2 is current week, sheet3 delivers all new or changed results based on columns A D and G. What I would like to add, or even modify, is the math portion for automatically. Currently, my results are copy the whole row from sheet2 and paste into sheet3. Ideally, I would like for it to subtract sheet2 H from sheet1 matching row H column. If match or error, new.
Below is the code I am currently using. I am also attaching raw file before code is ran, and an example output.
Thanks for any help!
Sub what_changed()
Dim ws1 As Worksheet, ws As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set ws4 = Worksheets("Helper")
ws3.Cells().ClearContents
ws4.Cells().ClearContents
wr = 1 'this will biul a List of "INdexNumbers" on sheet 4
For r = 2 To ws1.Range("A" & Rows.Count).End(xlUp).Row
ws4.Cells(wr, "A") = ws1.Cells(r, "A") & ws1.Cells(r, "D") & ws1.Cells(r, "F") & ws1.Cells(r, "G")
ws4.Cells(wr, "B") = ws1.Cells(r, "F") 'qty
wr = wr + 1
Next r
For r = 2 To ws2.Range("A" & Rows.Count).End(xlUp).Row
ino = ws2.Cells(r, "A") & ws2.Cells(r, "D") & ws2.Cells(r, "F") & ws2.Cells(r, "G")
If WorksheetFunction.CountIf(ws4.Range("A:A"), ino) = 0 Then 'add record as something changed
lr = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws3.Rows(lr).EntireRow.Value = ws2.Rows(r).EntireRow.Value
ws3.Cells(lr, "H") = qty
End If
Next r
End Sub
1862018621
I have a macro currently setup to find the change in an order I receive. Where sheet1 is previous week, sheet2 is current week, sheet3 delivers all new or changed results based on columns A D and G. What I would like to add, or even modify, is the math portion for automatically. Currently, my results are copy the whole row from sheet2 and paste into sheet3. Ideally, I would like for it to subtract sheet2 H from sheet1 matching row H column. If match or error, new.
Below is the code I am currently using. I am also attaching raw file before code is ran, and an example output.
Thanks for any help!
Sub what_changed()
Dim ws1 As Worksheet, ws As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set ws4 = Worksheets("Helper")
ws3.Cells().ClearContents
ws4.Cells().ClearContents
wr = 1 'this will biul a List of "INdexNumbers" on sheet 4
For r = 2 To ws1.Range("A" & Rows.Count).End(xlUp).Row
ws4.Cells(wr, "A") = ws1.Cells(r, "A") & ws1.Cells(r, "D") & ws1.Cells(r, "F") & ws1.Cells(r, "G")
ws4.Cells(wr, "B") = ws1.Cells(r, "F") 'qty
wr = wr + 1
Next r
For r = 2 To ws2.Range("A" & Rows.Count).End(xlUp).Row
ino = ws2.Cells(r, "A") & ws2.Cells(r, "D") & ws2.Cells(r, "F") & ws2.Cells(r, "G")
If WorksheetFunction.CountIf(ws4.Range("A:A"), ino) = 0 Then 'add record as something changed
lr = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws3.Rows(lr).EntireRow.Value = ws2.Rows(r).EntireRow.Value
ws3.Cells(lr, "H") = qty
End If
Next r
End Sub
1862018621