Forum Discussion
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
- PaulD0925Copper 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 ?