Forum Discussion
PaulD0925
Sep 10, 2025Copper Contributor
Excel Code trying to copy cell data from one sheet to another based on 2 criteria
Sheet "My Plan" Sheet "Tracker" I have several plans in external files that I have code for to select and import 1 to sheet "My Plan". I am using cell E6 as a named cell called PlanName and I sele...
Kidd_Ip
Sep 11, 2025MVP
How about this for sync data from "My Plan" to Tracker"
Sub UpdateTrackerFromMyPlan()
Dim wsPlan As Worksheet, wsTracker As Worksheet
Dim tbl As ListObject
Dim planName As String
Dim lastRowPlan As Long
Dim i As Long, r As ListRow
Dim planID As Variant, baselineDate As Variant, percentComplete As Variant
' Set worksheets
Set wsPlan = ThisWorkbook.Sheets("My Plan")
Set wsTracker = ThisWorkbook.Sheets("Tracker")
Set tbl = wsTracker.ListObjects("Table1")
' Get selected plan name
planName = wsPlan.Range("PlanName").Value
' Get last row in "My Plan" with data in column D (Unique ID)
lastRowPlan = wsPlan.Cells(wsPlan.Rows.Count, "D").End(xlUp).Row
' Loop through each row in Tracker table
For Each r In tbl.ListRows
If r.Range(1, tbl.ListColumns("Workstream / Project").Index).Value = planName Then
' Match found, now compare Unique ID
For i = 2 To lastRowPlan ' Assuming row 1 is header
planID = wsPlan.Cells(i, "D").Value
If planID = r.Range(1, tbl.ListColumns("Plan Unique ID").Index).Value Then
' Copy Baseline Date and % Complete
baselineDate = wsPlan.Cells(i, "I").Value
percentComplete = wsPlan.Cells(i, "L").Value
r.Range(1, tbl.ListColumns("Baseline Due Date").Index).Value = baselineDate
r.Range(1, tbl.ListColumns("Percentage Complete %").Index).Value = percentComplete
Exit For
End If
Next i
End If
Next r
MsgBox "Tracker updated successfully!", vbInformation
End Sub
PaulD0925
Sep 11, 2025Copper Contributor
This is excellent and almost works.
It runs, it identifies / matches the Unique ID correctly, and updates the table, however it updates with nothing, i.e. the values of baselineDate and percentComplete seem to be empty.
Any ideas ?