gowflow
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.
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
14 Experts available now in Live!