VBA & Macro Problems

How to Copy Calculated Values to the Next Empty Column in Excel VBA

Steven Parker Oct 13, 2026 868 views

Question details

The user needs to use an Excel VBA macro to copy calculated data from one worksheet and paste only the values into the first available blank column on a different worksheet.

How to Copy Calculated Values to the Next Empty Column with VBA

Product: Excel
Device & OS: not provided
Scenario: Automating a reporting or archiving process where calculation results are generated on a primary sheet and sequentially recorded in another worksheet, requiring the macro to dynamically locate the next empty column upon each click.
Observed behavior: The user requires a VBA solution that accurately identifies the end of the existing data, pastes only the values to avoid transferring underlying formulas, and iterates horizontally on subsequent runs.

Before you start

Ensure you have the Developer tab enabled in your Excel ribbon and macro security settings configured to allow running VBA scripts. Verify that your destination row has continuous data without unintentional blank cells, as gaps can cause the script to miscalculate the last used column.

Solution 1Recommended

Use the Range.End Method to Identify and Paste to the Next Column

This is the most efficient method, utilizing the xlToLeft parameter to dynamically find the last filled cell in a specific row and offsetting by one to paste the plain values directly.

By bypassing the clipboard and directly setting the destination values equal to the source values, this macro runs faster and prevents screen flickering. The script targets row 2 to determine the horizontal limit of your dataset.

Open the VBA Editor

Press the ALT + F11 shortcut keys simultaneously to open the Microsoft Visual Basic for Applications window.

Insert a New Module

From the top menu bar, click 'Insert' and select 'Module' to create a blank workspace for your macro.

Input the Macro Code

Paste the following script into the module window: Sub CopyPasteCalculations() Dim sourceRange As Range Dim destinationCell As Range Set sourceRange = Worksheets("CALCULATIONS").Range("B1:B15") Set destinationCell = Worksheets("OUTPUTS").Cells(2, Worksheets("OUTPUTS").Columns.Count).End(xlToLeft).Offset(0, 1) Application.ScreenUpdating = False destinationCell.Resize(sourceRange.Rows.Count, 1).Value = sourceRange.Value Application.ScreenUpdating = True End Sub

Customize the Sheet and Range Names

Modify 'CALCULATIONS' and 'B1:B15' to match your source data sheet and range. Adjust 'OUTPUTS' and the row index (currently set to 2 in the Cells function) to match your destination setup.

Run the Macro

Close the VBA editor. In your workbook, you can now press ALT + F8, select 'CopyPasteCalculations', and click 'Run' to append the data to the next empty column.

Use the Range.End Method to Identify and Paste to the Next Column

Performance Boost: The inclusion of Application.ScreenUpdating = False halts visual refreshes while the macro is processing, making the execution instantaneous.

Solution 2

Use the PasteSpecial Method for Complete Format Control

An alternative method that utilizes the standard Copy and PasteSpecial clipboard actions, ideal if you want to paste formats or column widths alongside the values.

Automate with WPS Spreadsheets

Run VBA Macros Seamlessly in WPS Spreadsheets

WPS Office provides robust support for VBA macros, allowing you to use exact Excel VBA scripts to automate tasks like copying calculated values without requiring code changes. Its familiar interface ensures you can transition and build automation smoothly.

1. Open WPS Spreadsheets: Launch WPS Office and open your .xlsm or .xlsb workbook.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is not visible, enable it from the WPS settings menu.
3. Open the VBA Editor: Click the 'VB Editor' button to access the coding environment, insert a module, and paste your copy-paste macro.
4. Execute Your Script: Click 'Macros' on the ribbon, select your new macro, and click 'Run' to automatically populate your next blank column.

Full compatibility with Microsoft Excel VBA syntax and objectsFamiliar Developer tab interface for writing and testing macrosLightweight architecture for faster macro executionFree and highly compatible alternative to Microsoft Excel

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro overwrite the last column instead of moving to a new one?

This happens if the reference row you are using to find the last column (e.g., row 2 in Cells(2, Columns.Count)) is empty in the target column. Ensure the row index specified accurately targets a row that always contains data in your pasted dataset.

Does this VBA script transfer formatting or just the numerical values?

The recommended script uses direct value assignment (.Value = .Value) and PasteSpecial xlPasteValues, which means only the raw numerical or text results of your formulas are pasted. No borders, colors, or formulas are transferred.

How can I trigger this macro with a single click?

You can assign the macro to a clickable button. Navigate to the Developer tab, click Insert > Form Controls > Button. Draw the button on your sheet, and when the 'Assign Macro' dialogue box appears, select your newly created script.

Can I copy the values to a completely different workbook?

Yes, but you will need to update the object references in the VBA script. Instead of just specifying Worksheets("OUTPUTS"), you must specify the workbook first, such as Workbooks("TargetBook.xlsx").Worksheets("OUTPUTS").

Excel & Data Problems