Solved: Making months and years from now.

[Sir Phoenix]

I want to accomplish the following.

I have an integer I.

from 0 to I, (0 being the current month, I being I months from now)

I want to generate a string with the month and year, check if there's a worksheet with that string name, if there is, good, if not, create it.

Also, to find a way if any of the current (month-year) worksheets are last month or before, find a way to select them for either removal or deletion.

Thanks for your help!

[Bob Phillips]

Try this

[VBA]
Dim i As Long
Dim sDate As String
dimdte As Date
Dim sh As Worksheet

Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
dte = DateSerial(Right(sh.Name, 4), Left(sh.Name, 2), 1)
If Month(dte) < Month(Date) Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

For i = 0 To 5
sDate = Format(DateSerial(Year(Date), Month(Date) + i, 1), "mm-yyyy")
If Not SheetExists(sDate) Then
Worksheets.Add(after:=Worksheets.Count).Name = sDate
End If
Next i


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

[/VBA]

[sheeeng]

Try this

[VBA]
Dim i As Long
Dim sDate As String
dimdte As Date
Dim sh As Worksheet

Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
dte = DateSerial(Right(sh.Name, 4), Left(sh.Name, 2), 1)
If Month(dte) < Month(Date) Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

For i = 0 To 5
sDate = Format(DateSerial(Year(Date), Month(Date) + i, 1), "mm-yyyy")
If Not SheetExists(sDate) Then
Worksheets.Add(after:=Worksheets.Count).Name = sDate
End If
Next i
[/VBA]

How does DateSerial works?

[Bob Phillips]

How does DateSerial works?

Same as the worksheet DATE function.

[Cyberdude]

For your dining and dancing pleasure, I am attaching a brief outline of the Date and Time functions (as I see them).

[Sir Phoenix]

I'm getting an error with the sheetExists function... how do I get/make it?

[Bob Phillips]

I'm getting an error with the sheetExists function... how do I get/make it?

Mea culpa!

I have now added it to the original reply above.

[Sir Phoenix]

*sigh* Unrelated, but it errors when it tries to automatically add a worksheet. Run-time error '1004': Method 'Add' of object 'Sheets' failed.

[Bob Phillips]

*sigh* Unrelated, but it errors when it tries to automatically add a worksheet. Run-time error '1004': Method 'Add' of object 'Sheets' failed.

[VBA]
Sub aa()
Dim i As Long
Dim sDate As String
Dim dte As Date
Dim Sh As Worksheet

On Error Resume Next
Application.DisplayAlerts = False
For Each Sh In ThisWorkbook.Worksheets
dte = DateSerial(Right(Sh.Name, 4), Left(Sh.Name, 2), 1)
If dte <> 0 And Month(dte) < Month(Date) Then
Sh.Delete
End If
Next Sh
Application.DisplayAlerts = True
On Error GoTo 0

For i = 0 To 5
sDate = Format(DateSerial(Year(Date), Month(Date) + i, 1), "mm-yyyy")
If Not SheetExists(sDate) Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sDate
End If
Next i

End Sub


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function
[/VBA]

[Sir Phoenix]

Awesome. This worked. Thanks!!!