Your question, your audience. Choose who sees your identityâ€”and your questionâ€”with question security.
Sub DailyUpdate()
Dim WSC As Worksheet
Dim WSD As Worksheet
Dim MaxRowC As Long, MaxRowD As Long, I As Long
Dim CRow As Long, DRow As Long
Dim cCell As Range
Set WSC = Sheets("Control")
MaxRowC = WSC.Range("A" & WSC.Rows.Count).End(xlUp).Row
Set WSD = Sheets("Dump")
MaxRowD = WSD.Range("A" & WSD.Rows.Count).End(xlUp).Row
'---> Run check on Dump
'---> Create Formula to find items in Dump but not in Control
' =IF(ISERROR(VLOOKUP(A2,Control!A:A,1)),A2,"")
WSD.Range("B2:B" & MaxRowD).Formula = "=IF(ISERROR(VLOOKUP(A2,Control!A:A,1)),A2,"""")"
WSD.Range("B2:B" & MaxRowD).Copy
WSD.Range("B2").PasteSpecial xlPasteValues
WSD.Range("A1:B" & MaxRowD).Sort Key1:=WSD.Range("B1"), order1:=xlDescending, Header:=xlYes
If WSD.Range("B2") <> "" Then
Set cCell = WSD.Range("B2:B" & MaxRowD).Find(what:="")
DRow = cCell.Row - 1
WSD.Range("B2:B" & DRow).Copy WSC.Range("A" & MaxRowC + 1)
WSC.Range("B" & MaxRowC + 1 & ":B" & MaxRowC + 1 + DRow - 1).Value = DateValue(Now)
End If
WSD.Range("B:B").Delete
'---> Run check on Control
'---> Create Formula to find items in Control but not in Dump
' =IF(ISERROR(VLOOKUP(A2,Dump!A:A,1)),A2,"")
WSC.Range("D2:D" & MaxRowC).Formula = "=IF(ISERROR(VLOOKUP(A2,Dump!A:A,1)),A2,"""")"
WSC.Range("D2:D" & MaxRowC).Copy
WSC.Range("D2").PasteSpecial xlPasteValues
WSC.Range("A1:D" & MaxRowC).Sort Key1:=WSC.Range("D1"), order1:=xlDescending, Header:=xlYes
If WSC.Range("D2") <> "" Then
Set cCell = WSC.Range("D2:D" & MaxRowC).Find(what:="")
CRow = cCell.Row - 1
WSC.Range("C2:C" & CRow).Value = DateValue(Now)
End If
WSC.Range("D:D").Delete
'---> Sort Control on Col A
WSC.Range("A1:D" & MaxRowC + DRow).Sort Key1:=WSC.Range("A1"), order1:=xlAscending, Header:=xlYes
WSC.Range("A1").Select
MsgBox "Daily Compare Done !", vbExclamation
End Sub
If a unique identifier number is in Orbit and in Checksheets, the update is to confirm the date in Column "U" in Orbit, and assign "completed" in Checksheets Column "X". If it is a new item in Orbit, it is to append the item to Checksheets and place todays date in Column "Y" in checksheets, so they know the date it was added.
When the item is appended, the associated items in the row from Orbit are to be added to the associated columns in Checksheets.
gowflow