I'm thinking this is a Macro...

I have a workbook with Sheets named 06-08-05, 06-09-05, etc. Everyday
when I open the workbook, I'd like to have a new sheet that automatically
gets created, is named with the next "date" (i.e., 06-10-05 in this
example) and copies all data from the previous days Sheet to this new one.
How?

Thanks so much for your help!!

David

Press ALT+F11, click on the "ThisWorkbook" module to the left, and paste the
code below in the window to the right:

Sub Workbook_Open()

Dim iTotalSheets As Long
Dim strLastSheet As String

iTotalSheets = ActiveWorkbook.Worksheets.Count
strLastSheet = Sheets(iTotalSheets).Name

Sheets(iTotalSheets).Copy After:=Sheets(iTotalSheets)
Sheets(iTotalSheets + 1).Name = Format(CDate(strLastSheet) + 1,
"mm-dd-yy")

End Sub

---

Press ALT+Q and save.

HTH
Jason
Atlanta, GA


"DNA" wrote:

I'm thinking this is a Macro...

I have a workbook with Sheets named 06-08-05, 06-09-05, etc. Everyday
when I open the workbook, I'd like to have a new sheet that automatically
gets created, is named with the next "date" (i.e., 06-10-05 in this
example) and copies all data from the previous days Sheet to this new one.
How?

Thanks so much for your help!!

David

Thanks a bunch. I am though, getting a syntax error with this line:

Sheets(iTotalSheets + 1).Name = Format(CDate(strLastSheet) + 1,
"mm-dd-yy")

MY BAD ... it was me, thanks SO MUCH!!!

One more thing. Each sheet has a date in B2, can I get that to change to
the next date as well?

I changed the code a little and added your request to the end:

Sub Workbook_Open()

Dim iTotalSheets As Long
Dim strLastSheet As String
Dim wsNewLastSheet As Worksheet

iTotalSheets = ActiveWorkbook.Worksheets.Count
strLastSheet = Sheets(iTotalSheets).Name

Sheets(iTotalSheets).Copy After:=Sheets(iTotalSheets)
Set wsNewLastSheet = Sheets(iTotalSheets + 1)
wsNewLastSheet.Name = Format(CDate(strLastSheet) + 1, "mm-dd-yy")

With wsNewLastSheet.[B2]
.Value = .Value + 1
End With

End Sub


---
HTH
Jason
Atlanta, GA


"DNA" wrote:

One more thing. Each sheet has a date in B2, can I get that to change to
the next date as well?

Where should the lines break correctly within the code? That's the problem
I'm having when pasting...

I messed up before, but the code should be pasted in as it looks now.

Jason

"DNA" wrote:

Where should the lines break correctly within the code? That's the problem
I'm having when pasting...

Got it, thanks again Jason!!