Excel VBA - Open monthly file names based on Month being worked on

[MHamid]

Hello,

I need some advise.

I want to automate some data entry in excel. We have a spreadsheet where data is manually entered into cells E15, E16, F15, F16, O15, O16, P15, P16, Y15, Y16, Z15, and Z16. Row 15 is for current month data and row 16 is for prior month data. There are two files that the user will have to open to gather this data. The filename would be something like "Filename_(Mar_Final)" for current month and "Filename_(Feb_Final)" for prior month. The month is the only things that changes in the filename.

I want to write a VBA code that will in the end be embedded into a button that the user would just click and all the information will populate. My concern would be how can I get the right filenames to open month after month?
As in if I am working on March, then I will need to open the Mar_Final file and the Feb_Final file. If I am working on April, I will need the Apr_Final file and the Mar_Final file. What is the best way to accomplish this task?

Let me know if you need any further clarification.

Thank you

 

[Johnny C]

Try

strMth = MonthName(month(Now()), True)
If month(NOW()) = 1 then
    strPriorMth = MonthName(12, True)
else
    strPriorMth = MonthName(month(Now())-1, True)
End If

 

[MHamid]

Hello,

Would that work even if the months name in the file are labeled as the first three letters of the month (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)?


Thank you

 

[Johnny C]

Yes.
The MonthName function has 2 parameters,

MonthName(month Number, Abbreviated)

the first is month number in the range 1-12 (which is why the prior month needs testing in case the current month is Jan which would give a prior month of 0 if we just did current month - 1)

The second tells Excel whether to use the full month name if the parameter is set to false (e.g. January) or a 3 letter abbreviated form if the parameter is set to True (e.g. Jan)