• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

ravikumar00008

New Member
Hi All,


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

[pre]
Code:
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
 
How's this?

[pre]
Code:
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]
 
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
 
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.
 
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
 
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.
 
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
 
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.
 
Back
Top