PDA

View Full Version : Solved: Making months and years from now.



Sir Phoenix
11-04-2005, 06:59 PM
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
11-05-2005, 05:32 AM
Try this


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

sheeeng
11-05-2005, 08:09 AM
Try this


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


How does DateSerial works? :friends:

Bob Phillips
11-05-2005, 08:40 AM
How does DateSerial works? :friends:

Same as the worksheet DATE function.

Cyberdude
11-05-2005, 12:57 PM
For your dining and dancing pleasure, I am attaching a brief outline of the Date and Time functions (as I see them).

Sir Phoenix
11-05-2005, 07:27 PM
I'm getting an error with the sheetExists function... how do I get/make it?

Bob Phillips
11-06-2005, 04:38 AM
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
11-06-2005, 04:56 PM
*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
11-07-2005, 01:25 AM
*sigh* Unrelated, but it errors when it tries to automatically add a worksheet. Run-time error '1004': Method 'Add' of object 'Sheets' failed.


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

Sir Phoenix
11-07-2005, 02:27 PM
Awesome. This worked. Thanks!!!