VBA & Macro Problems

How to Fix SumByColor Circular Reference After Copying an Excel Worksheet

Bushra Parveen Oct 9, 2026 869 views

Question details

The user needs to fix a circular reference error triggered by a custom SumByColor formula after copying an Excel worksheet to a new workbook.

How to Fix SumByColor Circular Reference After Copying an Excel Worksheet

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Copying a worksheet containing custom VBA functions to another workbook.
Observed behavior: A circular reference error occurs because the VBA function name defined in the code (e.g., SumColor) does not match the formula name used in the worksheet cells (e.g., SumByColor).

Before you start

Ensure you have access to the Developer tab to view your macro code and confirm that macros are enabled in your workbook settings.

Solution 1Recommended

Match the VBA Function Name with the Worksheet Formula

Resolve the circular reference by ensuring the custom function name in your VBA module identically matches the formula typed into your worksheet cells.

When copying a worksheet containing custom macros, a mismatch between the VBA code's function name and the cell formula can cause Excel to misinterpret the calculation, resulting in a circular reference error. Renaming either the code or the formula to match will instantly resolve this.

Open the VBA Editor

Press 'Alt + F11' on your keyboard, or navigate to the 'Developer' tab on the ribbon and click 'Visual Basic' to open the VBA Editor.

Locate the Custom Function

In the left-hand Project Explorer pane, find the module containing your custom color summing code and double-click it to view the script.

Rename Function or Update Formula

Check the declared function name (e.g., 'Function SumColor'). If your worksheet uses '=SumByColor(...)', change the VBA declaration to 'Function SumByColor'. Alternatively, update the formulas in your worksheet cells to use '=SumColor(...)' so they match the code exactly.

Save as Macro-Enabled File

Go to 'File' > 'Save As', and ensure you select 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu to preserve your VBA code.

Match the VBA Function Name with the Worksheet Formula

Recalculate Formulas: After updating the names, you may need to press 'F9' to manually force the workbook to recalculate and clear the circular reference warning.

Easily Manage VBA Macros with WPS Spreadsheet

WPS Spreadsheet offers comprehensive, built-in support for VBA macros. You can effortlessly create, edit, and debug custom functions like SumByColor without dealing with complex compatibility issues.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open the workbook containing your custom SumByColor formulas.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the main ribbon and click on 'VBA Editor'.
3. Correct the function names: Locate your module and ensure the function name in the code matches the formula used in your spreadsheet cells perfectly.
4. Save your work: Click 'Save As' and choose 'Macro-Enabled Workbook (*.xlsm)' to securely save your corrected VBA script.

Fully compatible with Microsoft Excel VBA macros (.xlsm and .xlsb formats).Advanced, user-friendly VBA Editor for quick code debugging and management.Lightweight architecture ensures fast calculation of complex custom formulas.Free and intuitive interface that closely mirrors Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why does copying a worksheet cause a circular reference?

When you copy a worksheet to a new workbook, named ranges and custom VBA functions might lose their proper reference context. If the copied macro code contains a function name that conflicts or fails to match the worksheet formula, the spreadsheet engine can misinterpret the calculation path, leading to a circular reference.

How do I save a file that contains custom VBA functions?

You must save the file as a Macro-Enabled Workbook (*.xlsm). If you attempt to save it as a standard Excel Workbook (*.xlsx), the VBA code will be completely stripped out upon saving, causing your custom formulas to return a '#NAME?' error the next time you open the file.

How do I enable the Developer tab to view my VBA code?

To enable the Developer tab, go to 'File' > 'Options' > 'Customize Ribbon'. In the right-hand list of Main Tabs, check the box next to 'Developer' and click OK. The tab will now appear on your ribbon, granting you access to the Visual Basic Editor and macro security settings.

Excel & Data Problems