Excel VBA: Copy Through the Last Row and Create Dynamic Charts
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.

- 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.
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.
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.
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.
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
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.

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.

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.




