How to find all the previous months from the current month ?

[ravikumar00008]

Hi All,


I am able to find out the current month and previous month by using this code and it is fine.

[pre]

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

Sheets(CurrMonth).Select
Sheets(PrevMonth).Select

[/pre]
If i want to find all the previous months sheets from the current month along with the current month.How i need to change the code.


Please help me to solve this problem


ex:

CurrMonth=July

PrevMonths=June,May,April,March,Feb,Jan.


Regards

Kumar

 

[Luke M]

How's this?

[pre]

Dim CurrMonth, PrevMonth, PrevMonths, ws As Worksheet, x

CurrMonth = Format(Date, "mmm")
NewDate = Date

'Build the string
Do While Format(NewDate, "mmm") <> "Jan"
NewDate = NewDate - Day(NewDate)
PrevMonth = Format$(NewDate, "mmm")
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like CurrMonth & "*" Then
CurrMonth = ws.Name
Exit For
ElseIf ws.Name Like PrevMonth & "*" Then
PrevMonths = PrevMonths & "," & ws.Name
Exit For
End If
Next
Loop
'Trim the extra comma
PrevMonths = Mid(PrevMonths, 2, 999)

'At this point you have the name of CurrMonth and all the
'Previous months going back to January

[/pre]

 

[ravikumar00008]

Hi Luke,


Thanks for the reply.


In my workbook,I have sheets like from Jan '12,Feb '12,.....Dec '12.

Now I need to find the current month and load the current month corresponding sheet data.Likewise all the previous months from the current month.


Regards

Kumar

 

[Luke M]

What do you mean by "load the data"?

Did the code I provide not give you the list? From your example, it looked like you wanted a list of sheet names.

 

[ravikumar00008]

Luke,


Of course.I can solve my problem by using your code.


But how i want is a loop which show the values from the current month(n) to the previous month(1).


ex:if month=july then

i need to get the values like from july to jan

month=july

month=june

month=may

month=april

month=March

month=feb

month=jan


Hope you got my point.


But with your code the loop is giving the values of only previous months.


Regards

Kumar

 

[Luke M]

When you say value, are you actually wanting to get a value from the worksheet, or just the sheet names?

If the latter, the code I posted above finds the CurrMonth and all the PrevMonths. If the former, I would recommend changing the code so that instead of storing sheet names, have the code store the desired value into some variable.

 

[ravikumar00008]

Luke,


You are right.Because my sheet names(some part) are with Jan to Dec.That is why i have explained like that in my post.


But the code what i have received from you really helps to me.


I have a macro i need to call that from the current month to the all the previous months.


This is a 2 step process now.But can't we make it in a single shot.like by reading in the same loop from the current month to the last previous month(jan).


Any how really thanks for your help.


Regards

Kumar

 

[Luke M]

I still don't know what you are asking that hasn't already been done.

What you have now:

Macro runs, has two final outputs. CurrMonth will contain the name of worksheet that most closely matches the 3 letter abbreviation of the current month. PrevMonths will contain a comma-delimited list of sheet names that match the 3 letter abbreviations of all months prior to current month going back to January. If you want the two variable combined into 1, a simple "&" could be used.

All of the work is done within a single loop, so I'm not sure what you mean by a "2 step process".


Please clarify exactly what it is you want and how it is different from what you have now. Thanks.