Need Macro to copy same cell from different named workbooks

[LikeButtah]

Each week we create a workbook with a date name "8.21.11xls.xls". This date relates to the end of the week for which we are calculating from. So the next week is 7 days from the last (ie 8.28.11xls.xls). I need a macro to pull a value from the same cells each week and paste them into another worksheet while looking for the date in that particular worksheet. Is this possible? Thanks.

<table border="0" cellpadding="0" cellspacing="0" width="426"><colgroup><col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:4461;width:92pt" width="122"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> </colgroup><tbody><tr style="mso-height-source:userset;height:18.0pt" height="24"> <td style="height:18.0pt;width:75pt" height="24" width="100">
</td> <td class="xl66" style="width:92pt" width="122">Total Cost</td> <td class="xl66" style="width:93pt" width="124">Total O/T</td> <td class="xl66" style="width:60pt" width="80">Total O/T % </td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">7/3/2011</td> <td class="xl68" align="right">$30,582.92</td> <td class="xl68" align="right">$2,645.93</td> <td class="xl67" align="right">8.7%</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">7/10/2011</td> <td class="xl68" align="right">$27,625.36</td> <td class="xl68" align="right">$2,080.37</td> <td class="xl67" align="right">7.5%</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">7/17/2011</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl67">
</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">7/24/2011</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl67">
</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">7/31/2011</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl67">
</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">8/7/2011</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl67">
</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl65" style="height:18.0pt" align="right" height="24">8/14/2011</td> <td class="xl68">
</td> <td class="xl68">
</td> <td class="xl67">
</td> </tr> </tbody></table>

 

[Smitty]

Welcome to the Board!

INDIRECT.EXT from the More-Func Add-In might fit the bill.

HTH,

 

[LikeButtah]

Smitty,
This does look like it would be great but when I type the path in (ex. =INDIRECT.EXT(O:\Payroll & Rollcall\[8-14-11xls.xls]Summary'!M9") I get an error with the "Rollcall\[8" highlighted. I guess its a syntax error but I don't know how to fix it. Any help would be appreciated. Thanks.

 

[LikeButtah]

Smitty,

Disregard, I got the right syntax but I'm getting a #Ref as the result.