Copying from one workbook to another

Hi everyone,

new on the forum and getting started with vba:

I was wondering if someone could help me with the following >>

I have two Excel files stored on in different folders for example
test09Feb12.xls is stored in C:\Program Files\blp\data
report09Feb12.xls is stored in C:\Program Files\blb\data

Each morning when running the macro I want the macro to go to these folders and copy the content of latest file saved there, or simply the file with the name (with the date of the previous working day).
The worksheets I want to copy are called for example sheet1 in test09Feb12 and sheet 2 in report09Feb12.

In want the macro to copy these two sheets to a new Excel file called Summary09Feb12.xls And the name of this file is adapted according to the corresponding date (previous working date).

After having done this I would like to save the new created Excel file to lets say C:\Program Files\reporting

After all this please save and close the two source files and keep the Summary file open.



Like I said, all new to VBA, I've read quite useful codes on the forum which relate to my problem so I hope someone is able to assist me...

Thanks in advance !!

VBA noobie

what have you tried so far?
which parts work?
which parts give error?
what does not work correctly?

Hi,

thanks for your reaction.

Well actually I have been trying with the Macro recorder, but not quite there yet. Like I said I'm a rookie.

I found some code in threat: "Copy data only from multiple workbooks to Workbook with mulitple sheets using Macros"
but not quite sure how to adapt it to my problem

I'm looking at it myself, but any tips or help will be greatly appreciated ..

Cheers

vb Code:

set bk1 = workbooks.open("C:\Program Files\blp\data\test" & format(date - 1, "ddmmmyy") & ".xls")
set bk2 = workbooks.open("C:\Program Files\blp\data\report" & format(date - 1, "ddmmmyy") & ".xls")
set newbk = workbooks.add
bk2.sheets("sheet2").copy newbk.sheets(1)
bk1.sheets("sheet1").copy newbk.sheets(1)
newbk.saveas "C:\Program Files\reporting\Summary" & format(date - 1, "ddmmmyy") & ".xls"
bk1.close false
bk2.close false

this is untested, will only look at files from the day previous, some modification will be needed to look for files if older, the 2 source files will be closed without changing
test this and see what modifications are required