Excel VBA Macro to Add or Update Multiple Dashboard Rows
Question details
Create a VBA macro that transfers multiple rows from an Input sheet to a Data sheet, updating matching shapes or adding new records.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A dashboard uses shape names in column A to identify records whose attributes are entered on an Input sheet.
- Observed behavior
- The desired macro processes all populated input rows rather than only row 2, then clears rows that were successfully transferred.
Check that the workbook has sheets named Input and Data, with headers in row 1 and shape names in column A. The example transfers columns A through D; adjust those ranges if your attributes occupy different columns.
Loop through input rows and match each shape
Use this macro to update the first matching shape on the Data sheet or append a new record when no match exists.
The macro compares trimmed shape names without regard to letter case. If the Data sheet contains duplicate shape names, it updates only the first match.
It copies values rather than formulas or formatting. Rows with a blank shape name are skipped and left on the Input sheet.
In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, press Alt+F11 where supported.
In the VBA editor, select Insert > Module and paste this code: Sub TransferDashboardRows() Dim wsIn As Worksheet, wsData As Worksheet Dim lastInput As Long, lastData As Long Dim r As Long, d As Long, target As Long Dim shapeKey As String, transferred As Long On Error GoTo TransferError Set wsIn = ThisWorkbook.Worksheets("Input") Set wsData = ThisWorkbook.Worksheets("Data") lastInput = wsIn.Cells(wsIn.Rows.Count, "A").End(xlUp).Row For r = 2 To lastInput shapeKey = Trim$(CStr(wsIn.Cells(r, "A").Value2)) If Len(shapeKey) > 0 Then lastData = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row target = 0 For d = 2 To lastData If StrComp(Trim$(CStr(wsData.Cells(d, "A").Value2)), shapeKey, vbTextCompare) = 0 Then target = d Exit For End If Next d If target = 0 Then target = lastData + 1 wsData.Range("A" & target & ":D" & target).Value2 = wsIn.Range("A" & r & ":D" & r).Value2 wsData.Cells(target, "A").Value2 = shapeKey wsIn.Range("A" & r & ":D" & r).ClearContents transferred = transferred + 1 End If Next r MsgBox transferred & " row(s) transferred.", vbInformation Exit Sub TransferError: MsgBox "Transfer stopped after " & transferred & " row(s): " & Err.Description, vbExclamation End Sub
Select File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Excel, select Developer > Macros, choose TransferDashboardRows, and select Run.
Open the Data sheet and verify updated and appended records. Return to Input to review any skipped rows or rows remaining after an error.

Work with dashboard spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files and move your spreadsheet work to WPS, while checking macro support and compatibility before relying on VBA.

Frequently Asked Questions
How does the macro decide whether to add or update a row?
It compares the shape name in Input column A with names in Data column A. It updates the first match or appends a row after the last used shape name if no match is found.
How can I transfer more than four columns?
In the VBA editor, change the A:D ranges used for copying and clearing to cover your attribute columns, such as A:F. Make sure the Input and Data columns have the same layout.
What happens to input rows without a shape name?
The macro skips rows whose column A value is blank after trimming spaces. Those rows remain on the Input sheet for review.
Why do I need to save the workbook as XLSM?
The XLSM format preserves VBA code. Select File > Save As and choose Excel Macro-Enabled Workbook; run macros only in workbooks you trust.
Does this macro copy formulas and formatting?
No. The Value2 assignment copies cell values only. Formulas are transferred as their current values, and formatting is not copied.




