Generate File Name Programatically

I have a series of files, all named sequentially by date, for example...

Sales Report - Week of 2009-07-27 - NH.xlsm
Sales Report - Week of 2009-08-03 - NH.xlsm
Sales Report - Week of 2009-08-10 - NH.xlsm

...and so on. (The dates are always Mondays.)

I want to get the contents of a particular range on the previous file (e.g. the "RaceResults" range a.k.a. B4:B33 on "Sales Report - Week of 2009-08-10 - NH.xlsm")

Right now, I've got the following fairly inelegant code that mostly gets the job done.

Please Login or Register  to view this content.

Obviously, the problem is the first line. I always want to grab data from the week before, but how do I generate the file name?

(If anybody knows a good way to manipulate the entire range at once - rather than all that FillDown/Copy/PasteSpecial nonsense - I'm game to hear that, too.)

Hi Timothy, and welcome to the forum.

The code below will open the previous Monday's workbook, copy the range, paste it to the current workbook and then close last week's. You shouldn't see it happen as I've set ScreenUpdating to False at the beginning of the macro.

Please Login or Register  to view this content.