VBA - Referencing file with changing date

[ConfusedaboutVBA]

Hi all,

I am trying to write a macro that copies data from a spreadsheet each month, however the name of the file will change each month to reflect the latest month. For example the spreadsheet will be titled 'Book1 Example 300613, then the next month the file will be titled Book1 Example 310713. Is there a way I can amend my code to pick up the latest month?

Please Login or Register  to view this content.

Thanks

[TMS]

One way, maybe:

Please Login or Register  to view this content.

Regards, TMS

[JBeaucaire]

The EOMONTH() formula will tell you the last day of any specific month.

This will tell you the last day of this month:
=EMONTH(TODAY(), 0)

This will tell you the last day of last month:
=EMONTH(TODAY(), -1)

...etc.

With that you have all you need to "look" for the file based on end of month dates, say for the last 3 months.

Please Login or Register  to view this content.