How to find the Current month and Previous month?

[ravikumar00008]

Hi,


if the workbook sheet names are like this "Jan '12"....."Jun '12","Jul '12"..etc

I am using this code to find out the current month and previous month sheets in my workbook

[pre]

sub test()
Dim CurrMonth As String, PrevMonth As String
CurrMonth = Format$(Date, "mmm 'yy")
PrevMonth = Format$(Date - Day(Date), "mmm 'yy")
sheets(CurrMonth).select
msgbox "current month",,currmonth
sheets(prevmonth).select
msgbox "previous month",,prevmonth
end sub

[/pre]
If the workbook sheet names are like this "Jan '12","Feb '12","Mar '12","April '12","May '12","June '12","Jul '12","Aug '12","Sept '12","Oct '12","Nov '12",

"Dec '12"


see these are not in the same format.


Now how to find the current month and previous month sheets from this kind of workbook.


Thanks in advance.


Regards

Kumar

 

[Hui]

Kumar


If the dates are Text, I would change the worksheet Names from Text to Real dates

Then use a Custom Number format to display then as MMM 'YY


The use your existing code

 

[Luke M]

Is it okay to rename the sheet names so that they all follow the same abbreviation standard?

[pre]

Sub Rename()
Dim NewName As String
For Each ws In ThisWorkbook.Worksheets
NewName = WorksheetFunction.Substitute(ws.Name, "'", "1, 20")
ws.Name = Format(DateValue(NewName), "mmm 'yy")
Next
End Sub

[/pre]

 

[ravikumar00008]

Hi Luke,


Thanks for your reply.


No we can't rename the sheet.Those are the exact sheet names what i have provided


Regards

Kumar

 

[shrivallabha]

Edit:= Oops. I don't think you can use my code.


If the pattern mentioned is consistent then try the following code to rename them all at once.


The code is untested.

[pre]

Public Sub UpdateItOnce()
For i = 1 To Sheets.Count
If Len(Sheets(i).Name) > 7 Then Sheets(i).Name = Left(Sheets(i).Name, 3) & " '" & Right(Sheets(i).Name, 2)
Next i
End Sub

[/pre]

 

[Luke M]

Well, if we can't rename the sheets, perhaps we can just calculate where the renamed sheet "would" be at.

[pre]

Sub Rename()
Dim NewName As String
Dim NameList As String
Dim CurrMonth As String
Dim PrevMonth As String
Dim SheetIndex As Integer

For Each ws In ThisWorkbook.Worksheets
NewName = WorksheetFunction.Substitute(ws.Name, "'", "1, 20")
'build a list of sheet names based on correct format
'ignores sheets that don't fit the date syntax
On Error Resume Next
NameList = NameList & Format(DateValue(NewName), "mmm 'yy")
On Error GoTo 0
Next
CurrMonth = Format$(Date, "mmm 'yy")
PrevMonth = Format$(Date - Day(Date), "mmm 'yy")

'Where does the correctly formatted name appear in list
x = InStr(1, NameList, PrevMonth, 1)
'Calculate the sheet's position
SheetIndex = (x - 1) / 7 + 1

'this will select the PrevMonth
Worksheets(SheetIndex).Select
End Sub

[/pre]

 

[shrivallabha]

If the sheets are in sequence then you can try:

[pre]

Public Sub Select()
' "Jan '12","Feb '12","Mar '12","April '12","May '12","June '12","Jul '12","Aug '12","Sept '12","Oct '12","Nov '12",
'"Dec '12"
Sheets(Month(Date)).Select
MsgBox Sheets(Month(Date)).Name, , "CurrMonth"
Sheets(Month(Date) - 1).Select
MsgBox Sheets(Month(Date) - 1).Name, , "PrevMonth"
End Sub

[/pre]

 

[Sajan]

Hi Kumar,

Just to give you one more option... the following does not require VBA


Setup a Name called "WorkSheetNames" in your workbook, and set the value to be the following:

={"Jan '12";"Feb '12";"Mar '12";"April '12";"May '12";"June '12";"Jul '12";"Aug '12";"Sept '12";"Oct '12";"Nov '12";"Dec '12"}


(Alternatively, you could setup a range somewhere in your workbook with these values, and refer the Name "WorkSheetNames" to that range.)


Then, the following formula will return the worksheetname for the current month:

=INDEX(WorksheetNames,MONTH(TODAY()),1)


The following formula will return the worksheetname for the previous month:

=INDEX(WorksheetNames,MONTH(TODAY())-1,1)


You can then access data on a given worksheet using the INDIRECT() function.

For example, the following formula will return the value in cell A1 from the worksheet for the current month.

=INDIRECT("'"&SUBSTITUTE( INDEX((WorksheetNames),MONTH(TODAY()),1), "'","''") & "'!A1")

entered with Ctrl+Shift+Enter

(Since you have single quotes in your worksheet name, the formula has to work around it. It is adding a single quote at the beginning, and substituting the single quote in the middle with two single quotes, so that the parameter to INDIRECT function will keep the quote char.)


The formula does not make any assumptions regarding the order of the worksheets.

(Note: It currently does not include any error checking for worksheet names beyond the 12 months indicated. But since that is straightforward, I will leave that as an exercise for you!)


Cheers,

Sajan.