VBA & Macro Problems

How to Copy Formulas to the Next Blank Table Using Excel VBA Macros

Nimra Malik Sep 25, 2026 869 views

Question details

The user needs an Excel VBA macro that can reliably identify the next blank table in a worksheet and automatically copy and extend formulas to the designated destination range.

How to Copy Formulas to the Next Blank Table Using Excel VBA Macros

Product: Excel
Device & OS: not provided
Scenario: Running a monthly report where a VBA macro needs to copy specific formulas to a new, empty table without overwriting existing data sections.
Observed behavior: The current macro lacks a reliable logical method to dynamically find the correct destination table based on blank rows or specific markers.

Before you start

Before modifying your VBA code, ensure you have saved a backup copy of your workbook and enabled the Developer tab in your spreadsheet software to access the Visual Basic Editor.

Solution 1Recommended

Use Consistent Markers to Locate the Next Blank Table in VBA

Define source and destination rows dynamically by instructing the macro to look for a specific table header, label, or empty row as a marker.

To accurately paste formulas into a new section, your VBA script must first identify where the current data ends and the new table begins. Relying on consistent markers ensures the macro dynamically targets the correct range without overwriting existing data.

Open the Visual Basic Editor

Press Alt + F11 to open the Visual Basic Editor, and locate the module containing your monthly macro.

Define Your Variables

Declare variables for your source range and destination rows (e.g., Dim ws As Worksheet, Dim destRow As Long).

Locate the Destination Table

Use the End(xlUp) or End(xlDown) methods, or a loop, to find the last used row and offset to the next blank row or specific header label (e.g., destRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 2).

Copy and Extend Formulas

Instruct the macro to copy the formulas from the source range (e.g., Column B) and apply them across the destination range up to the required column (e.g., Range("B" & destRow & ":AP" & destRow).Formula = Range("B2:AP2").Formula).

Use Consistent Markers to Locate the Next Blank Table in VBA

Test with a Sanitized File: Always test your new VBA logic on a sanitized sample workbook before applying it to your main monthly report to verify that formulas copy to the correct table.

Seamless VBA Support

Use WPS Spreadsheet to Run and Edit VBA Macros

WPS Office offers robust support for VBA macros. You can easily write, edit, and execute your formula-copying scripts in WPS Spreadsheet with an interface familiar to standard spreadsheet users.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm workbook containing the monthly data.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon of the WPS interface.
3. Open the VBA Editor: Click on 'Visual Basic' or press Alt + F11 to launch the built-in VBA editor.
4. Edit and Run Your Macro: Insert or modify your macro code to locate the next blank table, then click 'Run' to seamlessly copy and extend the formulas.

Native support for VBA and macro executionFully compatible with Microsoft Excel (.xlsm) macro-enabled formatsLightweight software for fast execution of complex scriptsFamiliar Developer tab and Visual Basic Editor interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro overwriting existing data instead of finding the next blank table?

This typically happens if the macro uses absolute cell references instead of dynamic ones. Ensure you are using relative referencing like Cells(Rows.Count, 1).End(xlUp).Row combined with an Offset to properly target the next empty table.

How do I extend copied formulas across multiple columns in VBA?

Once you have determined the starting cell of your destination range, you can assign the formula directly to the entire horizontal range. For example, using Range("B" & destRow & ":AP" & destRow).Formula = sourceRange.Formula will apply it across columns B through AP.

Can I run my existing Excel VBA macros in WPS Office?

Yes, WPS Spreadsheet supports VBA. You can open your existing .xlsm files in WPS Office and run most standard Excel VBA macros, including those for copying and pasting formulas, without needing to rewrite the code.

Excel & Data Problems