VBA Express : Excel - Insert a new sheet each time the workbook is opened

Excel

Insert a new sheet each time the workbook is opened

Ease of Use

Easy

Version tested with

2000

Submitted by:

lucas

Description:

Each time you open the workbook, a new sheet is added. Alternate code provided to insert new sheet from a template.

Discussion:

You are logging the number of spool pieces and welds completed each day and wish to keep a copy of the days activities on one sheet for reference or proof. Sometimes printed copies get lost or trashed and you need to be able to go back to a certain days activities to show what was done on that day. This will allow you to do that.

Code:

instructions for use

Put this code In the ThisWorkbook code pane: Option Explicit Private Sub Workbook_Open() Sheets.Add End Sub 'ALTERNATE CODE TO OPEN NEW SHEET BASED ON A TEMPLATE 'comment the code above to use this code which will 'add a new sheet each time the workbook is opened based on 'a template. The template sheet can be named anything 'as long as you change it in the code also 'The sheets visable True and False allow you to hide the 'template sheet. Be sure to set up your template 'before you run this or you will get an error 'Private Sub Workbook_Open() 'Application.ScreenUpdating = False 'Sheets("Template").Visible = True 'Sheets("Template").Select ' Sheets("Template").Copy Before:=ActiveSheet ' 'Sheets("Template").Copy Before:=ActiveSheet 'Sheets("Template").Visible = False 'Application.ScreenUpdating = True 'End Sub

How to use:

Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
Click on the ThisWorkbook object in the project explorer
In the module pane paste the code above.
Close the Visual Basic Editor By clicking the X in the upper right corner or go to File-Close
Save your file.

Test the code:

Close your workbook and reopen it. A new sheet will be added. Instructions for using a template are included in the code.

Sample File:

add_new_worksheet_each_time_opened.zip 6.62KB

Approved by mdmackillop

This entry has been viewed 117 times.