VBA & Macro Problems

How to Create an Excel VBA Macro to Copy Sheets and Assign Sequential Dates

Huda Qurayshi Oct 8, 2026 869 views

Question details

The user wants to automate duplicating an Excel worksheet multiple times, naming each new sheet with sequential dates starting from a designated date in the original sheet.

How to Create an Excel VBA Macro to Copy Sheets and Assign Sequential Dates

Product: Excel
Device & OS: not provided
Scenario: Creating multiple monthly or daily tracking sheets where each copied tab needs a unique, sequentially incremented date as its name.
Observed behavior: Manually copying sheets and renaming them with dates is tedious and time-consuming. A VBA macro is required to read a starting date, duplicate the sheet, and automatically apply sequential date names.

Before you start

Before running the macro, ensure you have a starting date entered in a specific cell (such as H1) on your active sheet, and save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Write a VBA Macro to Duplicate Sheets with Sequential Dates

Use a VBA script to prompt the user for the number of copies, read the base date from a designated cell, duplicate the sheet, and rename it with the next consecutive date.

This script takes a starting date from cell H1 of your active worksheet. It then asks how many copies you want to make, duplicates the active sheet that many times, and assigns the next chronological date to both the tab name and cell H1 of the newly created sheet.

Open the VBA Editor

In Excel, press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click 'Insert' in the top menu bar and select 'Module'. This will open a new blank window where you can paste your macro code.

Paste the VBA Code

Copy the following code and paste it into the module window: Public Sub DuplicateSheetMultipleTimes() Dim ws As Worksheet Dim n As Long, numtimes As Long Dim d As Date On Error Resume Next n = InputBox("How many copies of the active sheet do you want to make?") If n >= 1 Then Set ws = ActiveSheet d = ws.Range("H1").Value + 1 For numtimes = 1 To n ws.Copy After:=Worksheets(Worksheets.Count) With Worksheets(Worksheets.Count) .Name = Format(d, "dd.mm.yyyy") .Range("H1").Value = d End With d = d + 1 Next numtimes End If End Sub

Run the Macro

Close the VBA Editor. In your Excel workbook, ensure you are on the sheet you want to copy and that cell H1 contains your starting date. Press ALT + F8, select 'DuplicateSheetMultipleTimes', and click 'Run'.

Write a VBA Macro to Duplicate Sheets with Sequential Dates

Date Format Tip: The macro formats the tab names as 'dd.mm.yyyy'. You can change this format string in the code (e.g., 'mm-dd-yyyy' or 'yyyy-mm-dd') to suit your regional preferences.

Efficient Macro Support in WPS Office

Automate Sheet Duplication in WPS Spreadsheets

WPS Office provides excellent built-in support for VBA macros, allowing you to run custom scripts like sequential date sheet copying effortlessly. It offers a highly compatible and familiar interface that makes transitioning from Excel seamless.

1. Open Your Workbook in WPS: Launch WPS Spreadsheets and open your .xlsm file containing the data you want to duplicate.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, you can enable it from the WPS options menu.
3. Open the Macro Editor: Click on 'Macros' or press ALT + F11 to launch the WPS VBA Editor.
4. Insert and Run the Code: Insert a new module, paste your sequential date copying macro, and run it directly within WPS to instantly generate your daily or monthly tracking sheets.

Fully compatible with Microsoft Excel (.xlsm) macro filesBuilt-in developer tools to seamlessly edit and run VBA scriptsLightweight application that processes heavy macros quicklyFamiliar ribbon interface for easy navigation and shorter learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting an error stating 'That name is already taken' when running the macro?

This error occurs if a worksheet with the calculated date name already exists in your workbook, or if the starting date cell is empty (which causes Excel to generate a default base date). Ensure your target cell (e.g., H1) contains a valid date and that the workbook doesn't already contain sheets with the upcoming dates.

How can I change the date format on the copied sheet tabs?

In the provided VBA script, locate the line `.Name = Format(d, "dd.mm.yyyy")`. You can modify the "dd.mm.yyyy" text to your preferred date layout, such as "yyyy-mm-dd" or "mmm-dd-yyyy". Note that Excel sheet names cannot contain slashes (/), asterisks (*), or brackets ([]).

Can I use this macro to duplicate a sheet based on the current sheet's name instead of a cell?

Yes. You can modify the code to extract the base date directly from the active sheet's name by changing the variable assignment to read `d = DateValue(ws.Name)`. However, ensure your original sheet's name is in a format that VBA can easily recognize as a valid date.

Excel & Data Problems