copying files

[STEVEB]

Each month I copy 20 division files into a consolidated spreadsheet and
e-mail the spreadsheet to a user group.

The files are saved in a division folder (for example: C: Division 1)
by month.
The file names are Division 1 expenses_July 2005.

The consolidated spreadsheet has a tab for each division and uses prior
month data.

Is there a way to have a Macro:

1) Find the prior month expense file for each division on the C: Drive
and copy the information to the appropriate tab on the consolidated
spreadsheet.

For example the consolidated spreadsheet for August 2005 would have a
macro copy the Division 1 expenses_July 2005 information onto the
Division 1 tab and repeat for all 20 divisions.

Since the file name does not change (except the date), the big issue is
getting Excel to identify the proper period (i.e the month) and copy the
prior month to the current period spreadsheet.

Any help would be greatly appreciated!

 

[Guest]

Start by selecting the current month using a variable.

Ex.
Dim dteMonth as Date
Dim strMonth as String

dteMonth = Date()
'This will get you the previous month.
dteMonth = DateAdd("m",-1,dteMonth)
Sheets(" & dteMonth & ").Select

You can format the strMonth variable any way you want.
strMonth = dteMonth
strMonth = Format(dteMonth,"mmm,yyyy")