Need Help With A Macro

D

DNA

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
 
J

Jason Morin

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
 
D

DNA

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

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

DNA

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

Jason Morin

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
 
D

DNA

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

Jason Morin

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

Jason
 
Top