VBA & Macro Problems

How to Move Expired Schedule Columns to Archive Using Excel VBA

Elise WilliamsElise Williams Oct 9, 2026 869 views

Question details

The user needs an Excel macro to automatically transfer past-date schedule columns to an archive sheet and keep the archived data sorted chronologically.

How to Move Expired Schedule Columns Using an Excel VBA Macro
Product
Excel
Device & OS
not provided
Scenario
Managing a dynamic schedule worksheet by archiving columns associated with dates that have already passed.
Observed behavior
Expired schedule columns currently remain on the active sheet. The goal is to evaluate the date in row 2 of each column, move expired columns to an 'Old Schedule' sheet, delete them from the main sheet, and sort the archive by date.
Before you start

Create a duplicate of your schedule workbook before running the macro, as VBA column deletions clear the system's undo history. Ensure that the dates in your columns are formatted as actual Date values rather than text.

Solution 1Recommended

Create a Custom VBA Macro to Archive and Sort Expired Columns

Write and execute a VBA script to evaluate schedule dates, transfer expired data to a secondary sheet, and automatically reorganize the archive.

This solution utilizes Excel's built-in Visual Basic for Applications (VBA) to automate the column transfer process. By looping through the columns, the script checks if the date has passed, moves the relevant data, and finally applies a left-to-right sort on the archive sheet.

1
Open the VBA Editor

Press the ALT + F11 keys simultaneously in your open schedule workbook to launch the Visual Basic for Applications editor.

2
Insert a New Module

In the Project Explorer panel on the left side of the editor, right-click on your workbook's name, navigate to Insert, and select Module to open a blank coding window.

3
Draft the Copy and Delete Logic

Write a subroutine that loops through the columns on the 'Schedule' sheet. Use an 'If' condition to check if the cell value in row 2 is less than the current date. If true, use the 'Copy' method to send the entire column to the first empty column on the 'Old Schedule' sheet, and then delete the original column.

4
Implement the Sorting Function

At the end of your script, add a sorting action targeting the 'Old Schedule' sheet. Use the 'Range.Sort' method with the 'Orientation' parameter set to 'xlSortRows' (or 'xlLeftToRight') to sort the archived columns chronologically based on the dates in row 2.

5
Run the Archiving Macro

Close the VBA editor and return to your workbook. Press ALT + F8 to open the Macro dialog box, highlight your newly created archiving macro, and click Run to process the columns.

Create a Custom VBA Macro to Archive and Sort Expired Columns
Save as Macro-Enabled Workbook: To ensure your VBA code is saved for future use, go to File > Save As and select 'Excel Macro-Enabled Workbook (*.xlsm)' as the file format.
Automate Schedules Easily

Use WPS Spreadsheet to Run Your Archiving Macros

WPS Office provides excellent built-in support for VBA macros in its Spreadsheet application, allowing you to seamlessly run, edit, and manage your column-archiving scripts in a lightweight environment.

  1. 1. Install WPS Office: Download and install the WPS Office suite, ensuring your version includes VBA functionality.
  2. 2. Open Your Schedule File: Launch WPS Spreadsheet and open your existing .xlsm schedule file.
  3. 3. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' or 'VBA Editor' to view your archiving script.
  4. 4. Execute the Macro: Select your column-archiving macro from the list and click Run to automatically move and sort your expired schedule data.
Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Execute advanced VBA scripts to automate column archiving and data management.Lightweight, fast, and highly intuitive interface for handling complex schedule tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro copying the expired columns?

This usually happens if the date format in your target cell (e.g., row 2) is stored as text rather than a valid Excel date. The macro cannot correctly compare a text string to the current date. Ensure you format the cells as Dates.

Can I undo a macro after it deletes the expired columns from my main schedule?

No, actions executed by VBA macros clear Excel's undo history. Once the macro deletes the columns, you cannot simply press CTRL+Z to restore them. This is why testing the script on a backup file is critical.

How do I trigger this macro automatically when I open the workbook?

You can automate the script by calling your macro from the 'Workbook_Open' event. To do this, double-click 'ThisWorkbook' in the VBA editor and insert your macro call inside the 'Private Sub Workbook_Open()' procedure.