Update links macro

[Guest]

I have a workbook A comprising one worksheet that is linked to another
workbook B of six sheets; there are several links from A to each sheet in B.

Workbook B is generated by a macro on another workbook and saved down with
the same name so it overwrites the previous workbook B. However the sheet
names change slightly to reflect the date of creation; so the first sheet
will change from Sheet1Name+old date to Sheet1Name+new date and
Sheet2Name+old date to Sheet2Name +new date and so on.

I have workbook A open as i am running a macro already on that workbook and
then go to the other workbook to run a macro to create a new workbook B. How
do i get workbook A to update all the links automatically to the new workbook
B. I can get to the dialogue box 'Select Sheet' 'select the sheet to link
to:' Is it possible to use a macro to select each sheet in turn in the
dialogue box.

If it is possible i will be really grateful for the relevant code.

Many thanks

 

[Gary Keramidas]

maybe you could use some code like this to create your link based on the sheet
name instead of hard coding it
sheets(1) is the first sheet, so if the order is always the same, the name won't
matter.


nm = Sheets(1).Name
Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4"

if the sheet name (nm) changes, the formula still works

 

[Guest]

Sounds very good to me, i think i follow what you say which is very clever,
would you then get the macro to stick that (adapted of course for each link
etc) = "='" & nm & "'!" & "A4" (relevant cell ref)
into each cell which currently has an Excel formula in namely = path &
workbookname & sheet etc & A4


--
with kind regards

Spike

maybe you could use some code like this to create your link based on the sheet
name instead of hard coding it
sheets(1) is the first sheet, so if the order is always the same, the name won't
matter.


nm = Sheets(1).Name
Sheets("sheet2").Range("a2").Formula = "='" & nm & "'!" & "A4"

if the sheet name (nm) changes, the formula still works

 

[Gary Keramidas]

post back if you run into any issues

--


Gary

Sounds very good to me, i think i follow what you say which is very clever,
would you then get the macro to stick that (adapted of course for each link
etc) = "='" & nm & "'!" & "A4" (relevant cell ref)
into each cell which currently has an Excel formula in namely = path &
workbookname & sheet etc & A4

 

[Gary Keramidas]

in some applications, i need to build the formulas on the fly, so they're in an
auto_open macro. when the workbook is opened, the relevant formulas are created.

--


Gary

Sounds very good to me, i think i follow what you say which is very clever,
would you then get the macro to stick that (adapted of course for each link
etc) = "='" & nm & "'!" & "A4" (relevant cell ref)
into each cell which currently has an Excel formula in namely = path &
workbookname & sheet etc & A4

 

[Guest]

Thank you very much for your help works ok from this end, really grateful,
off for the weekend now will jproperly code it all up on monday and i am sure
will be fine, but appreciate your help.