PDA

View Full Version : Add New Worksheet to Include Date



kkal117
10-03-2008, 03:10 PM
I have this code to add a new worksheet based on a template called "POs" when I open the workbook. How do I modify the code so that the new inserted worksheet includes the the current date in the following format yyyymmdd...eg. 20081003 for October 3, 2008. Currently, the new sheet is named "POs (1)", "POs (2)" and so forth.



Private Sub Workbook_Open()

Application.ScreenUpdating = False
Sheets("POs").Visible = True
Sheets("POs").Select
Sheets("POs").Copy After:=ActiveSheet
Sheets("POs").Visible = False
Application.ScreenUpdating = True
End Sub

Demosthine
10-03-2008, 03:47 PM
Good Afternoon.

When you say you want it so that the "inserted worksheet includes the current date" I am assuming you want the Worksheet's Name to be "POs 20081003. Based on that, you would use:


Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Sheets("POs")
.Visible = True
.Select
.Copy After:=ActiveSheet
.Visible = False
End With

Sheets("POs (2)").Name = "POs " & _
Replace(Format(Now(), "yyyy/mm/dd"), "/", "")
Application.ScreenUpdating = True
End Sub


Any problems, let me know.
Scott

kkal117
10-03-2008, 04:35 PM
Wow, works perfectly. Thank you so much for your help!