Fix VBA Code That Copies Variable Columns and Hides Formulas in WPS Office Spreadsheets
Question details
The user needs a VBA macro that copies a variable-width header range from a Route worksheet to a Sections worksheet, writes formulas across the matching columns, and hides those formulas using cell formatting or worksheet protection.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A VBA procedure must transfer variable-width header rows and formula columns between worksheets without hard-coded column references, then hide the formulas from end users.
- Observed behavior
- The existing VBA code may rely on a fixed last column such as EA, so it fails or copies incorrect ranges when the source header width changes, and formulas remain visible to users.
Open the workbook in WPS Office Spreadsheets, enable the Developer tab, and confirm that the source worksheet (for example, Cable Route) and destination worksheet (for example, Sections) exist with the expected header row and data rows.
Use Dynamic Last Column Detection and Worksheet Protection to Hide Formulas
Use this method when the Route sheet has a variable number of header columns and you need the copied formulas to be hidden from users.
This method avoids hard-coded ranges such as EA by finding the last used column in row 1. It copies only the required header rows with Resize, writes formulas across the matching columns, and then applies worksheet protection with FormulaHidden so users cannot see or edit the formula cells.
In WPS Office Spreadsheets, click the Developer tab, open the Visual Basic Editor, insert a standard module, and create a Sub procedure.
Declare the source and destination worksheets, then use Set wsRoute = ThisWorkbook.Worksheets("Cable Route") and Set wsSections = ThisWorkbook.Worksheets("Sections"). Find the last used column with lastColumn = wsRoute.Cells(1, wsRoute.Columns.Count).End(xlToLeft).Column.
Use wsRoute.Range("A1").Resize(2, lastColumn).Copy Destination:=wsSections.Range("B1") to copy headers. Find the last data row with lastRow = wsRoute.Cells(wsRoute.Rows.Count, "A").End(xlUp).Row, build the formula text, and assign it with wsSections.Cells(2, 2).Resize(lastRow - 1, lastColumn).Formula = formulaText.
Set the formula range .Locked = True and .FormulaHidden = True, then call wsSections.Protect Password:="", UserInterfaceOnly:=True to hide formulas from the formula bar and protect them from editing.
Hide Formula Cells with a Custom Number Format Instead of Sheet Protection
Use this alternative when you only need to hide formula results on the sheet and do not require locking or protection.
Run the VBA Fix in WPS Office Spreadsheets
Use WPS Office Spreadsheets to run the VBA macro that detects variable header columns, copies ranges, writes formulas, and hides them with worksheet protection or a custom number format.
- 1. Install and open WPS Office Spreadsheets: Download WPS Office, install it, and open the macro-enabled workbook in WPS Office Spreadsheets.
- 2. Enable the Developer tab: Click the ribbon settings, enable the Developer tab, and open the Visual Basic Editor.
- 3. Insert and paste the macro code: Create a module, paste the dynamic VBA code, and replace worksheet names, source columns, and comparison logic to match the workbook.
- 4. Run and verify the macro: Run the macro, switch to the Sections worksheet, and confirm the variable header columns are copied and formulas are hidden.
Frequently Asked Questions
Why does my macro fail when the Route sheet gets more columns?
A common cause is a hard-coded range such as EA. Replace it with wsRoute.Cells(1, wsRoute.Columns.Count).End(xlToLeft).Column so the macro finds the last used column in row 1 automatically.
How do I hide formulas so users cannot see them in the formula bar?
Set .FormulaHidden = True and .Locked = True on the formula cells, then protect the worksheet. Protection must be applied after the format flags are set.
Can I hide formula results without protecting the sheet?
Yes, select the formula cells and set a custom NumberFormat of ;;;. This hides the displayed values, but the formula is still visible in the formula bar when selected.
How do I copy only the header rows and not the entire worksheet?
Use Range("A1").Resize(2, lastColumn).Copy Destination:=... to copy a two-row header range across the dynamically detected column count.
Does WPS Office Spreadsheets support this Excel VBA code?
WPS Office Spreadsheets supports many Excel VBA macros and macro-enabled workbooks. Open the workbook in WPS Office, enable the Developer tab, and test the code in the Visual Basic Editor.
