VBA & Macro Problems

How to Create Automatically Recurring Events in an Excel Calendar

Aamir Naveed Akram Oct 16, 2026 869 views

Question details

The user wants to generate and display automatically recurring events (weekly, monthly, yearly) in an Excel calendar based on a separate event list.

How to Create Automatically Recurring Events in an Excel Calendar

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic calendars based on event lists with complex recurring schedules.
Observed behavior: Formula-only setups struggle to expand recurring events into distinct calendar dates accurately, requiring an automated list-generation method.

Before you start

Ensure you have a clearly structured source table with distinct columns for the event name, start date, end date, and recurrence frequency before setting up your macro or query.

Solution 1Recommended

Use a VBA Macro to Generate Recurring Event Dates

Use a VBA script to automatically parse recurrence rules and generate individual event entries for your calendar to reference.

Because standard Excel formulas are limited when expanding a single rule into multiple rows, a VBA macro is the most effective approach. It can loop through your event list and create exact dates based on your start, end, and recurrence settings.

Prepare the master table

Create a master events table with columns for Event Name, Start Date, End Date, and Recurrence Type (e.g., Weekly, Monthly).

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Write the loop script

Insert a new Module from the Insert menu and write a script to loop through the master table, calculate the recurring dates, and output them into a new 'Expanded Dates' worksheet.

Link the calendar

Point your calendar's VLOOKUP or XLOOKUP formulas to this newly generated 'Expanded Dates' sheet so that it correctly displays every instance of the recurring event.

Use a VBA Macro to Generate Recurring Event Dates

Macro Security: Make sure to save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve and run the VBA code.

Solution 2

Use Power Query to Expand Dates

Utilize Power Query to generate lists of dates from start to end, which is ideal if you prefer avoiding VBA programming.

Manage Calendars Effortlessly

Create Dynamic Calendars with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros and advanced data processing, allowing you to seamlessly create recurring event calendars. Enjoy high compatibility with Microsoft Excel while managing your schedules.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing calendar workbook or start from a new spreadsheet.
2. Enable Developer Tools: Go to the Tools tab and click Developer to access the VBA macro editor for running your recurrence scripts.
3. Apply Templates: Alternatively, explore the WPS Template library to download pre-built dynamic calendars to save time.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Built-in VBA editor to run recurring event macros smoothly.Free, lightweight, and user-friendly interface.Abundant free calendar templates to get started quickly.

Frequently Asked Questions

Can I use standard formulas to create recurring calendar events?

While simple sequences can be done with newer dynamic array formulas like SEQUENCE, expanding complex recurring events with specific start and end boundaries generally requires VBA or Power Query to generate a reliable reference list.

How do I handle events that occur on the Nth weekday of a month?

In a VBA macro, you can use date math functions like DateSerial and Weekday to calculate exact dates for complex rules, such as 'the 3rd Wednesday of the month,' before appending them to your generated list.

Will my VBA calendar macro run on Excel for Web?

No, VBA macros are not supported in web-based spreadsheet applications. To view or run the macro that updates your recurring event dates, you must use the desktop version of Excel or WPS Office.

Excel & Data Problems