Forum Discussion

PaulD0925's avatar
PaulD0925
Copper Contributor
Sep 10, 2025

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 select the name of the plan from a dropdown.

Column D which is "Unique ID"

Column I which is "Baseline Date"

Column L which is "% Complete"

Sheet "Tracker" is a Table called "Table1"

I am looking for code (which I will activate by a button) which will look on sheet "Tracker" column Name "Workstream / Project" and if it doesn't match "PlanName" then go to the next row. If it matches then it needs to compare from sheet "My Plan" column D with sheet "Tracker" field "Plan Unique ID". If they are the same it needs to copy sheet "My Plan" column I and L to sheet "Tracker" fields "Baseline Due Date" and "Percentage Complete %".

Great if you can help

2 Replies

  • 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's avatar
      PaulD0925
      Copper 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 ?

Resources