How to Move Expired Schedule Columns to Archive Using Excel VBA
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.

- 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.
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.
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.
Press the ALT + F11 keys simultaneously in your open schedule workbook to launch the Visual Basic for Applications editor.
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.
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.
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.
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.

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. Install WPS Office: Download and install the WPS Office suite, ensuring your version includes VBA functionality.
- 2. Open Your Schedule File: Launch WPS Spreadsheet and open your existing .xlsm schedule file.
- 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. Execute the Macro: Select your column-archiving macro from the list and click Run to automatically move and sort your expired schedule data.

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.




