VBA & Macro Problems

Excel VBA Macro to Add or Update Multiple Dashboard Rows

Khadija KhanKhadija Khan Nov 18, 2026 868 views

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.

Add or Update Multiple Dashboard Rows with Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, press Alt+F11 where supported.

2
Insert the macro

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

3
Save and run the macro

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.

4
Check the results

Open the Data sheet and verify updated and appended records. Return to Input to review any skipped rows or rows remaining after an error.

Loop through input rows and match each shape
If the transfer stops: The macro clears each input row only after writing its data. If an error occurs during a write, check that Data row before rerunning; a partial update may have occurred.
Free Microsoft Office alternative

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.

Review and edit dashboard data in a familiar spreadsheet interfaceCompatible with Microsoft Excel formats such as XLSX and XLSMFree, lightweight alternative for everyday spreadsheet workOpen existing spreadsheets to make migration easier
microsoft office alternative - wps office

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.