VBA & Macro Problems

Create an Excel Macro to Move Values in a Repeating Pattern

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs a VBA macro that shifts specific cell values following a repeating interval pattern (e.g., A2 to B1, A5 to B4, A8 to B7) while moving only one sequence per macro run.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the relocation of specific data points from one column to another based on a fixed interval, processing one step at a time for better control.
Observed behavior: The user wants a script that tracks the number of times it has been executed, calculates the next target cell in the interval, moves its value one row up and one column right, and clears the original cell.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) before testing, and back up your original data since VBA actions generally cannot be reversed using the standard Undo command.

Solution 1Recommended

Use a Run-Tracking VBA Macro to Move Cells

This method uses a VBA script that tracks execution runs to calculate the next target cell (e.g., skipping every 3 rows), moving it up and right while clearing the source.

To achieve a step-by-step movement, the macro must remember its previous state. This can be done by defining a static variable within the VBA code or by storing a counter value in a hidden cell in your worksheet.

Open the VBA Editor

Navigate to the Developer tab and click on 'Visual Basic', or press ALT + F11 on your keyboard.

Insert a New Module

In the VBA Editor window, click 'Insert' from the top menu and select 'Module' to create a blank script canvas.

Define the Macro Logic

Write a subroutine with a Static variable (e.g., `Static RunCount As Integer`). Calculate the target row using a formula like `Row = 2 + (RunCount * 3)` to target rows 2, 5, 8, etc.

Add Movement and Clear Commands

Add the command to move the data: `Cells(Row - 1, 2).Value = Cells(Row, 1).Value`, then clear the original data with `Cells(Row, 1).ClearContents`. Increment `RunCount` by 1 at the end of the script.

Assign the Macro to a Button

Close the VBA editor. On your worksheet, go to the Developer tab, click 'Insert', choose a Button (Form Control), draw it on the sheet, and assign your new macro to it.

Pattern Customization: If your pattern starts on a different row or jumps by a different interval (e.g., every 4 rows instead of 3), simply adjust the calculation formula in your VBA code.

WPS Spreadsheet Macro Automation

Automate Repeating Data Patterns with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to run, edit, and create complex automation scripts. You can easily set up repeating pattern movements with a highly compatible, built-in VBA editor.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Access the Developer Tools: Click on the Developer tab in the ribbon and select 'VBA Editor' to open the coding environment.
3. Paste Your Script: Insert a new module from the top menu and paste your run-tracking macro code into the window.
4. Execute the Macro: Save your code and run the macro directly, or map it to a shortcut button on your sheet to process your pattern sequence one click at a time.

Fully compatible with Microsoft Excel macro formats (.xls, .xlsx, .xlsm).Built-in VBA editor supports standard Excel macro code natively.Lightweight installation with a highly intuitive user interface.Easily assign macros to custom buttons or shapes for quick data processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the macro work after I close and reopen the file?

You may have saved the file as a standard Excel Workbook (.xlsx), which automatically strips out all macro code. Always ensure you 'Save As' a Macro-Enabled Workbook (.xlsm) or Binary Workbook (.xlsb).

Can I undo a macro after it moves my cell data?

No, standard undo (Ctrl+Z) does not work for actions performed by VBA macros. It is highly recommended to save a copy of your worksheet data before running an untested script.

How do I change the macro to move the cell two columns over instead of one?

You need to modify the target column index in your VBA code. For example, change `Cells(Row - 1, 2)` to `Cells(Row - 1, 3)` to output the data into column C instead of column B.

How do I enable the Developer tab to access the VBA editor?

In Excel or WPS Spreadsheet, go to Options (File > Options), select 'Customize Ribbon,' and check the box next to 'Developer' in the main tabs list on the right pane.

Excel & Data Problems