Solved: Adding New Sheet w/ Sequential Date

[YellowLabPro]

Each calendar month there is a workbook w/ a worksheet for each day. The sheet name is the month and day, Mar 1, Mar 2, Mar 3, etc...
To accomplish this I start off w/ Mar, then Ctl, Select and drag. I must do this for each day of the month for each month. A bit weary after awhile.
So Looping, (my favorite topic), appears to be the way to do this.

I have recorded a macro to see how Excel handles this, but is very primitive. I need some help creating the variables to achieve this.

I am thinking that I need something like Sheet + 1 to create the next calendar day, but also if the possibility of referencing the month/year to automatically assign how many days are in that particular month.

[Recorded Macro]
Sub ChangeSheetName()
'
' ChangeSheetName Macro
'
Sheets("Oct 1 (2)").Select
Sheets("Oct 1 (2)").Copy After:=Sheets(2)
Sheets("Oct 1 (3)").Select
Sheets("Oct 1 (3)").Copy After:=Sheets(3)
Sheets("Oct 1 (4)").Select
Sheets("Oct 1 (4)").Copy After:=Sheets(4)
Sheets("Oct 1 (5)").Select
Sheets("Oct 1 (5)").Copy After:=Sheets(5)
End Sub

[Bob Phillips]

Sub ChangeSheetName()
Dim i As Long

With ActiveWorkbook
For i = 1 To 31
'test if we need to prematurely end the loop
'e.g. in Feb, Apr
If Month(CDate(DateSerial(Year(Date), Month(Date), i))) <> Month(Date) Then
Exit For
Else
.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
.ActiveSheet.Name = Format(CDate(DateSerial(Year(Date), Month(Date), i)), "mmm d")
End If
Next i
End With
End Sub

[YellowLabPro]

Bob,
That is spectacular. I for the first time was able to envision what was needed, and what some of the statements would look like, eg. For i = 1 to 31.
But no way could begin to put it together. Thanks so much for doing that and showing me how....

So doggon cool!

Best,

YLP

[cgannm]

Sub ChangeSheetName()
Dim i As Long

With ActiveWorkbook
For i = 1 To 31
'test if we need to prematurely end the loop
'e.g. in Feb, Apr
If Month(CDate(DateSerial(Year(Date), Month(Date), i))) <> Month(Date) Then
Exit For
Else
.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
.ActiveSheet.Name = Format(CDate(DateSerial(Year(Date), Month(Date), i)), "mmm d")
End If
Next i
End With
End Sub

I used this code and works great. How can I modify this code to create sheets for next the month. For example, current month is June but want create sheets to have next month's dates.

please advise.

[Charlize]

Sub ChangeSheetName()
Dim i As Long
Dim vdate As Date
'date in cell will determine which month
'should be created
vdate = Range("A1").Value
If vdate <> Empty Then
With ActiveWorkbook
For i = 1 To 31
'test if we need to prematurely end the loop
'e.g. in Feb, Apr
If Month(CDate(DateSerial(Year(vdate), Month(vdate), i))) <> Month(vdate) Then
Exit For
Else
.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
.ActiveSheet.Name = Format(CDate(DateSerial(Year(vdate), Month(vdate), i)), "mmm d")
End If
Next i
End With
End If
End Sub

[cgannm]

Thanks, Charlize. I am trying not hardcode date. I would like to obtain the next month(date) based on the current date.

I tried Month(Date) +1 but the return date is Jan. Not sure why?

[lucas]

What if you use this in cell A1
=NOW()+30

[lucas]

this seems to work in the code:
vdate = Now + 30

[Charlize]

I think we need to check the current month and add 1. Try this one : Sub testnextmonth()
Dim no As Long
Dim nextmonthdate As Date
no = Month(Date) + 1
If no = 13 Then
no = 1
nextmonthdate = DateSerial(Year(Date) + 1, no, 1)
Else
nextmonthdate = DateSerial(Year(Date), no, 1)
End If
MsgBox "Next month is : " & Month(nextmonthdate)
End Sub

[Charlize]

this seems to work in the code:
vdate = Now + 30Not sure but what if it's the 1st of january ?

[cgannm]

Solved! I removed CDate and added Month(Date) + 1.

Thanks!

[Charlize]

Solved! I removed CDate and added Month(Date) + 1.

Thanks!And what if it's december ?

[geekgirlau]

Mind if I throw a spanner in the works at this point? :devil2:

WHY do you have a separate sheet for each date?

Having worked with data in this format, I can tell you that performing any data analysis involves recombining the data that you've gone to so much effort to separate. IMO you can accomplish a lot more by having a single data sheet containing a date field, and pivot table(s) rather than having to scroll through 57,002 sheets to view the date that you want, not to mention the fact that you end up with a much smaller and more efficient workbook. :2p: