Dynamic Year

[Dummy Excel]

Hi All,
I'm going crazy trying to get this logic right...

I have a report that needs to be done monthly. The macro finds the previous month, opens it and copies the data into my new report for history. The problem im having is come Jan 11, its looking for the previous report as Dec 11.

Scenario for the predefined Values are:

YY	11
PreYY	10
CurMnth	Jan
PriorMnth	Nov
PreMnth	Dec
Customer	ABC
Business	XYZ

    If YY = "11" And CurMnth = "Jan" And PreMnth = "Dec" Then
        Windows(Customer & " " & Business & " OF " & PriorMnth & " " & PreYY & ".xls").Activate
    Else
        Windows(Customer & " " & Business & " OF " & PriorMnth & " " & YY & ".xls").Activate
    End If

So my question is how can I make the "11" (in bold) dynamic so come 2012, I don't need to make any changes?

thanks in advance
Samuel

 

[Blade Hunter]

shg4421 - maybe ive missed something here...

your code works well if the month is Jan 11, although if the month is Feb 11, I still get Dec 10 when it should be Jan 11

Did mine work? If not what happened when you ran it?

 

[Dummy Excel]

hey Dan, tried your code before and it didnt work, although I tried again and it works...thanks again for your help

 

[Blade Hunter]

No probs mate, always happy to help .

To explain the code it takes the date of today and replaces the day with 01, it then takes away 1 from the result giving us always the last day of last month .