Pivot Table Issues

Copy Excel Pivot Table Results to Another Sheet with VBA in WPS Office

Anum Mushtaq Nov 13, 2026 869 views

Question details

The user needs to copy a monthly Excel pivot table result from column D to the correct month column on a YTDChart worksheet using VBA, but the source contains #REF! errors and the source and destination ranges differ.

Copy Excel Pivot Table Results to Another Sheet with VBA in WPS Office

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: A monthly pivot table report must be transferred to a year-to-date chart worksheet with the correct month column using a VBA macro.
Observed behavior: The source pivot formulas contain #REF! references, and the source and destination ranges are not identical, so a direct copy can copy invalid formula results or fail.

Before you start

Before running or recording the VBA macro, identify the cells in the source column D that show #REF! and locate the matching month column on the YTDChart worksheet so the repaired references and destination address are known.

Solution 1Recommended

Repair #REF! references before copying pivot results with VBA

Use this solution first when the pivot source formulas show #REF!, because VBA cannot reliably copy a valid calculated result from a broken formula.

VBA reads the formula result that Excel or WPS Spreadsheets calculates. When a cell contains #REF!, the cell no longer points to a valid range, so the macro copies the error value instead of the monthly pivot result.

Repairing the formula references or rebuilding the pivot connection restores the calculated value before the copy step.

Open the workbook in WPS Office Spreadsheets

Launch WPS Office Spreadsheets and open the workbook that contains the source pivot table and the YTDChart worksheet.

Select the source pivot result cells in column D

Click the first result cell in column D that needs to be copied, then inspect the formula bar to see whether the formula contains #REF!.

Replace invalid references with the correct range

Edit the formula and replace the #REF! portion with the correct sheet name and cell range, then press Enter and confirm the cell displays the expected monthly value.

Repeat for remaining #REF! cells

Check the remaining cells in the source column D and correct each invalid reference before the macro is run.

Repair #REF! references before copying pivot results with VBA

Repair completed: After all source cells display valid values, the VBA copy can read the correct pivot results instead of carrying over #REF! errors.

Solution 2

Record and run a Paste Special Values VBA macro

Use this solution when the destination should contain only the calculated value, not the original pivot formula, and the source and destination ranges need to be adjusted in VBA.

Fix Pivot #REF! Errors and Copy Values with VBA in WPS Spreadsheets

WPS Office Spreadsheets can open and edit Excel pivot workbooks, repair #REF! references, and run or record VBA macros that copy pivot table results to a YTDChart worksheet as static values.

1. Open the Excel workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets, click Menu > Open, and select the .xlsx or .xlsm workbook containing the pivot table and YTDChart worksheet.
2. Find and repair #REF! errors in the pivot source column: Select the result cells in column D, read the formula bar, and replace any #REF! reference with the correct worksheet and cell range so each cell shows a valid value.
3. Open the VBA editor and add the copy macro: Press Alt+F11 to open the VBA editor, insert a module if needed, and enter a macro that selects the repaired source range, copies it, and uses PasteSpecial xlPasteValues at the YTDChart month cell.
4. Run the macro to update YTDChart: Return to the spreadsheet and press Alt+F8, choose the macro, and click Run to copy the pivot result value to the correct month column.
5. Verify the destination value and save the workbook: Confirm the YTDChart month cell shows the expected value and no formula, then save the file in WPS Office Spreadsheets or as an Excel-compatible format.

Directly repairs #REF! pivot references before copying monthly resultsRecords or runs VBA macros so you can automate the YTDChart updatePreserves Excel workbook compatibility when opening and saving .xlsx and .xlsm filesCopies values only to avoid carrying source formulas to the destination sheetProvides a familiar Excel-like interface for pivot table and macro tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA copy #REF! instead of the pivot result?

VBA copies the cell's calculated result. If the source formula contains #REF!, the cell result is the #REF! error, so the macro copies that error value instead of a usable monthly result.

How do I record a Paste Special Values macro in WPS Spreadsheets?

Enable the Developer tab, click Record Macro, perform a manual copy with Ctrl+C, right-click the destination cell, choose Paste Special and Values, then click Stop Recording and edit the generated VBA ranges if needed.

Can I copy pivot table results without copying the source formulas?

Yes, use Paste Special > Values or the VBA PasteSpecial xlPasteValues method so only the calculated value is placed on the YTDChart worksheet.

What should I do when the source and destination ranges are different sizes?

Copy only the same size block or one source cell and paste into the top-left destination month cell, then adjust the recorded VBA source and destination addresses to match the exact ranges.

Does WPS Office support Excel VBA macros for pivot tables?

WPS Office Spreadsheets supports VBA macro editing and recording on compatible Windows installations, so you can open an Excel macro-enabled workbook, repair pivot references, and run or adjust copy macros.

Excel & Data Problems