VBA & Macro Problems

Excel VBA: Copy Through the Last Row and Create Dynamic Charts

Maira MehtabMaira Mehtab Nov 18, 2026 868 views

Question details

Create an Excel macro that copies pivot table output through the last used row and adjusts a chart to match the data size.

Copy Through the Last Row and Update Charts with Excel VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A pivot table can grow or shrink, so the copied range and chart source should not rely on a fixed row count.
Observed behavior
The desired result is a copied range and chart source that reflect the current pivot output.
Before you start

Open the workbook in desktop Excel and identify a column that is populated through the last pivot output row. The example assumes headers are in A1:B1, the source sheet is named Data, and a dedicated destination sheet is named Output.

Solution 1Recommended

Find the last row and update the chart with VBA

Use this macro when you want to copy columns A and B and resize a chart source each time the macro runs.

The macro finds the last nonempty cell in column A, copies A1:B through that row, and creates or updates a chart on the Data sheet. It clears columns A and B on the Output sheet first, so use a dedicated destination sheet.

The chart updates when the macro runs; it does not automatically resize after a pivot refresh unless you run the macro again or arrange for it to run after the refresh.

1
Prepare the worksheets

Name the source sheet Data and create a dedicated sheet named Output. Confirm that column A has a value on the last row you want to copy and that columns A and B contain the intended chart data.

2
Add the macro

In desktop Excel, open the VBA editor with Alt+F11, choose Insert > Module, and paste this code: Sub CopyPivotAndUpdateChart() Dim ws As Worksheet, dest As Worksheet Dim lastRow As Long Dim chartObj As ChartObject Set ws = ThisWorkbook.Worksheets("Data") Set dest = ThisWorkbook.Worksheets("Output") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then MsgBox "No data rows found in column A." Exit Sub End If dest.Columns("A:B").ClearContents ws.Range("A1:B" & lastRow).Copy Destination:=dest.Range("A1") On Error Resume Next Set chartObj = ws.ChartObjects("PivotDataChart") On Error GoTo 0 If chartObj Is Nothing Then Set chartObj = ws.ChartObjects.Add(Left:=350, Top:=20, Width:=480, Height:=300) chartObj.Name = "PivotDataChart" End If chartObj.Chart.ChartType = xlColumnClustered chartObj.Chart.SetSourceData Source:=ws.Range("A1:B" & lastRow) End Sub

3
Run and verify

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Run CopyPivotAndUpdateChart from Developer > Macros, then check the Output sheet and the chart on Data. Refresh the pivot table and run the macro again whenever its size changes.

Find the last row and update the chart with VBA
Check pivot layout: The last used row in column A may include a grand total or other content below the pivot table. Adjust the source range if you want to exclude totals or use the PivotTable object's TableRange1 property to identify the pivot output.
Free Microsoft Office alternative

Work with Spreadsheet Charts in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for opening spreadsheets and working with charts. Its Microsoft Office format compatibility makes it easier to move existing files; check macro support before relying on an Excel VBA workflow.

Create and edit spreadsheet charts in WPS SpreadsheetsOpen and work with Microsoft Excel formats such as XLSXUse a familiar spreadsheet interface for easier migrationAccess a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

How does the macro find the last used row?

The expression ws.Cells(ws.Rows.Count, "A").End(xlUp).Row starts at the bottom of column A and finds its last nonempty cell. Choose a different column if A does not reliably contain the final data row.

Will the chart resize automatically when I refresh the pivot table?

Not with the sample macro alone. After using PivotTable Analyze > Refresh, run CopyPivotAndUpdateChart again, or configure an appropriate refresh event to run the update.

How can I find the pivot table range instead of the last used worksheet row?

In VBA, use the PivotTable object's TableRange1 property to reference its output without page fields. Check whether its headers and totals are appropriate for your copy and chart.

Can an Excel Table make a chart expand automatically?

Yes. Select a separate, ordinary data range and choose Insert > Table, then create a chart from that table. Adding rows to the table can expand the chart source; an Excel Table cannot overlap an existing pivot table.