updating sheets from another workbook

[JD-dunks]

I'm looking for a bit of code which updates sheets from other excel books which I'm downloading regularly.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I.e. <o></o>
<o> </o>
Look for workbook with naming convention and latest date<o></o>
update sheets in current workbook from the latest downloaded workbook with the same sheet name in this other workbook…<o></o>
<o> </o>
I don't want to update cell by cell, I want to replace the entire sheet for a number of sheets<o></o>
<o> </o>
Purpose being I have a report template which pulls data from the underlying sheets which I want to regular update.<o></o>
<o> </o>
Is there something quick for this?

 

[JD-dunks]

Just further to this....
I recorded a macro which quite crudely opens my test sheet and copies the data in to the sheet I want it, although I'm looking for something a little more sophisticated… I.e. something that looks for a filenameconvention with the report date.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o></o>
<o>This is what I did...</o>
<o> </o>
Sub UpdateData()<o></o>
ChDir _<o></o>
"I:\Barra Reports\Joey Test"<o></o>
Workbooks.Open Filename:= _<o></o>
"I:\Barra Reports\Joey Test\wfej-FD Asia ex-Japan-20110731.xls"<o></o>
Sheets("Risk Decomposition").Select<o></o>
Cells.Select<o></o>
Selection.Copy<o></o>
Windows("FD Asia ex Japan (wfej).xls").Activate<o></o>
Sheets("Risk Decomposition Report").Select<o></o>
Cells.Select<o></o>
ActiveSheet.Paste<o></o>
Sheets("Report").Select<o></o>
End Sub<o></o>

 

[perco754]

Hello,

Here's how to do a filesearch including naming filter etc. using VBA:

http://www.ozgrid.com/VBA/loop-through.htm

I would also recommend that you use objects for the 2 workbooks in order to prevent possible conflicts while copying etc. + when using objects you can avoid using the Select method.

    Dim wbSource As Workbook, wbDest As Workbook

BR,
perco