How can I get Excel to automatically change file path links?

I am trying to create an identical folder to one I already use. The catch is
that all of the linked worksheets in this new 'copied' folder must recoginze
the new folder name, not the old. I assume that excel can do this but
everything I have tried so far doesn't work.

This is what I want done:
The folder c:\FY2006 is copied so that there is a c:\FY2007. I have
multiple worksheets within 'FY2006' that are linked to other worksheets,
within 'FY2006', that I would like to automatically become linked within
'FY2007.' Every attempt I have done so far copies all of the workbooks to
FY2007, but the links for each workbook still have the file path of FY2006.

From FY2007, click on:
<Edit> <Links> <Change Source>

And navigate to FY2007 in the window, and link it to itself.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"iupsk" <[email protected]> wrote in message
news:[email protected]...
>I am trying to create an identical folder to one I already use. The catch
>is
> that all of the linked worksheets in this new 'copied' folder must
> recoginze
> the new folder name, not the old. I assume that excel can do this but
> everything I have tried so far doesn't work.
>
> This is what I want done:
> The folder c:\FY2006 is copied so that there is a c:\FY2007. I have
> multiple worksheets within 'FY2006' that are linked to other worksheets,
> within 'FY2006', that I would like to automatically become linked within
> 'FY2007.' Every attempt I have done so far copies all of the workbooks to
> FY2007, but the links for each workbook still have the file path of
> FY2006.

Would be possible to make a macro of this for monthly updated data? I have a file that must summarize the monthly situation and every month the input files change for new ones in a next month named folder.