Excel

Add a sheet with today's date

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Whenever this code is run a sheet named with the current date is selected, or if the sheet does not exist, the sheet is created. 

Discussion:

I have worked with a lot of users who were adding a new sheet to their workbook every week with the current date. This code automated an already simple but tedious process by adding a new sheet with the current date. The code checks to ensure that another sheet of the same name is not added by attempting to activate the sheet, and based on whether it exists or not, the sheet is added. 

Code:

instructions for use 

			
Option Explicit 
 
Sub AddSheets_Today() 
     '   Add and name a sheet with today's date
    Dim szToday As String 
     
     '   Date Stamp:
    szToday = Format(Date, "mmm-dd-yy") 
     
    On Error GoTo MakeSheet 
     
     '   Check if sheet already exists:
     '   if it does, select activate it
    Sheets(szToday).Activate 
     
     '   No errors, we are done
    Exit Sub 
     
     
MakeSheet: 
     '   If the sheet doesn't exist:
     '   Add it
    Sheets.Add , Worksheets(Worksheets.Count) 
     '   Name it
    ActiveSheet.Name = szToday 
End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. Go to TOOLS > MACRO > MACROS
  2. When the dialog appears, select {AddSheets_Today}
  3. Press Run
 

Sample File:

AddTodaySheet2.zip 7.06KB 

Approved by mdmackillop


This entry has been viewed 209 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express