Hi everyone I'm new here, I have searched everywhere for a solution but couldn't find one. In summary I have two workbooks, first one is the Tracker which is updated daily with new information and the second one is the Database where I store all the necessary information, both contain quite a lot of columns.
I am trying to build a macro that would take the request ID (C2) in the Tracker and find it in the Database workbook (Column D). If a request ID already exists in the Database the macro would update the latest information from the Tracker. However, if the request is not in the database a new row or rows would be filled out with both the request ID and the associated information.
I am using this code and have modified it to the intended purpose where it can create new entries by pasting the values (information) if request ID is not in the database. However as there can be 1, 2, 3 or 4 rows of data (a request may have up to 4 sub requests so it is broken down into 4 rows with the same request id), I am having trouble trying to get the code to find the row or rows where the request IDs are located and replace its associated information, for example this request ID ZX2211 has 3 rows, each row contains different information, does anyone know how to write the code such that it will go to the row where the request id is and then update all the values in the subsequent row or rows? Any help is very much appreciated!
Dim wb As Workbook, wsCopy As Worksheet, wsDest As Worksheet, i As Integer, j As Integer, rFndCell As Range, stFnd As String
Application.ScreenUpdating = False
Set wb = Workbooks.Open("Database path")
Set wsCopy = ThisWorkbook.Worksheets("Summary")
Set wsDest = Workbooks("Database.xlsx").Worksheets("Database")
stFnd = wsCopy.Range("C2").Value
j = wsDest.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
With wsDest
Set rFndCell = .Range("D:D").Find(stFnd, LookIn:=xlValues)
For i = 2 To 5
If rFndCell Is Nothing And wsCopy.Cells(i, "C").Value <> "" Then
With wsDest
.Range("B" & j).Value = wsCopy.Range("A" & i).Value
.Range("C" & j).Value = wsCopy.Range("B" & i).Value
.Range("D" & j).Value = wsCopy.Range("C" & i).Value
.Range("E" & j).Value = wsCopy.Range("D" & i).Value
.Range("F" & j).Value = wsCopy.Range("E" & i).Value
.Range("G" & j).Value = wsCopy.Range("F" & i).Value
.Range("H" & j).Value = wsCopy.Range("G" & i).Value
.Range("I" & j).Value = wsCopy.Range("H" & i).Value
.Range("J" & j).Value = wsCopy.Range("I" & i).Value
.Range("K" & j).Value = wsCopy.Range("J" & i).Value
.Range("L" & j).Value = wsCopy.Range("K" & i).Value
.Range("M" & j).Value = wsCopy.Range("L" & i).Value
.Range("N" & j).Value = wsCopy.Range("M" & i).Value
.Range("O" & j).Value = wsCopy.Range("N" & i).Value
.Range("P" & j).Value = wsCopy.Range("O" & i).Value
j = j + 1
End With
End If
Next
wb.Save
wb.Close
End With
End If
End Sub