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

 

[diddi]

you can use the function

Year(Now()) to find out which year is the current one and use that to set the value of YY

 

[shg]

I think you're making it hard on yourself:

    Const Customer As String = "Ajax"
    Const Business As String = "Mousetraps"
    Dim tDateThis As Date
    Dim tDateLast As Date
    
    tDateThis = #1/1/2011#
    tDateLast = DateAdd("m", -1, tDateThis)
    
    MsgBox Customer & " " & Business & " OF " & Format(tDateLast, "mmm yy") & ".xls"

 

[Dummy Excel]

Hi All - I was just about to add the below, as it will make it easier where I am coming from ...

    YY = format(DateSerial(Year(Now), Month(Now), Day(Now)), "yy")
    PreYY = format(DateSerial(Year(Now) - 1, Month(Now), Day(Now)), "yy")
    CurMnth = WorksheetFunction.Proper(format(DateSerial(Year(Now), Month(Now), Day(Now)), "Mmmm"))
    PriorMnth = WorksheetFunction.Proper(format(DateSerial(Year(Now), Month(Now) - 2, Day(Now)), "MMM"))
    PreMnth = WorksheetFunction.Proper(InputBox("Enter Previous Month as Mmm", "Month", "Jan"))

 

[Blade Hunter]

You could do this without an if:

Dim PriorMnth As Date
PriorMnth = DateAdd("D", -1, "01/" & Month(Now) & "/" & Year(Now()))
Windows(Customer & " " & Business & " OF " & Format(PriorMnth, "Mmm") & " " & Format(PriorMnth, "YY") & ".xls").Activate

Edit: Hehe Dan's late to the party once again

 

[shg]

You don't need all those variables -- all you need is one date variable that you can format as you like as needed, and calculate the prior month from that.

 

[Dummy Excel]

shg4421 - so how do I use my IF statement as when we are in Feb 11, the previous month will be Jan 11?

Hey Dan - As you can see im still learning

 

[Blade Hunter]

shg4421 - so how do I use my IF statement as when we are in Feb 11, the previous month will be Jan 11?

Hey Dan - As you can see im still learning

All good Sam, how are you going?

My solution will take care of that for you

 

[shg]

You don't need an If statement:

    Const Customer As String = "Ajax"
    Const Business As String = "Mousetraps"

    Dim tDateThis As Date
    Dim tDateLast As Date
    
    tDateThis = #1/1/2011#
    tDateLast = DateAdd("m", -1, tDateThis)
    
    Workbooks(Customer & " " & Business & " OF " & Format(tDateLast, "mmm yy") & ".xls").Activate

 

[Dummy Excel]

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