Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying files
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! -- STEVEB ------------------------------------------------------------------------ STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872 View this thread: http://www.excelforum.com/showthread...hreadid=396972 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying files
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") "STEVEB" wrote: 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! -- STEVEB ------------------------------------------------------------------------ STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872 View this thread: http://www.excelforum.com/showthread...hreadid=396972 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying from different files into 1 | Excel Discussion (Misc queries) | |||
Copying Files | Excel Discussion (Misc queries) | |||
Copying files | Excel Worksheet Functions | |||
Copying files in VB | Excel Programming | |||
Copying files from PC to PC | Excel Programming |