How to find the current month and previous month sheets in a workbook?

ravikumar00008

New member
Joined
Jun 13, 2012
Messages
20
Reaction score
0
Points
0
Hi All,


if the workbook sheet names are like this "Jan '12"....."Jun '12","Jul '12"..etc (All are in the same format)
I am using this code to find out the current month and previous month sheets in my workbook
Code:
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
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
 
Last edited:

ravikumar00008

New member
Joined
Jun 13, 2012
Messages
20
Reaction score
0
Points
0
Hi,

I got the solution like this
Dim CurrMonth, PrevMonth, ws As Worksheet, x
CurrMonth = Format$(Date, "mmm")
PrevMonth = Format$(Date - Day(Date), "mmm")
For Each ws In Sheets
If ws.Name Like CurrMonth & "*" Then
CurrMonth = ws.Name
ElseIf ws.Name Like PrevMonth & "*" Then
PrevMonth = ws.Name
End If
Next

Regards
Kumar
 

ravikumar00008

New member
Joined
Jun 13, 2012
Messages
20
Reaction score
0
Points
0
Hi All,

Additional thing what i want is,

Right now i am able to identify the current month and previous month sheets.It is fine.
I want another way also,Like
I need to find all the previous months sheets from the current month along with the current month.

ex:
CurrMonth=July
PrevMonths=June,May,April,March,Feb,Jan.

Regards
Kumar
 
Top