Changing source on multiple workbooks

We have one main workbook that the supervisor fills out that lists jobs,
times staff etc. I've just set up links to the daily worksheet workbooks
that automatically fill in which staff member is doing which jobs each
day.

But the problem is, the supervisor makes a copy and changes the name of
the main workbook weekly so that the sources for the other 7 workbooks
are now wrong.

I've shown him how to go editlinkschange source but he is
continuously forgetting how to do it and thinks this takes too long.
Does anyone know a macro that will automatically change the sources for
the other 7 workbooks using a command button on the main book? I began
designing a form which he can type the name of the new workbook from a
command button that simultaneously opens the 7 workbooks. But I have no
idea how to change source with information from a text box. Is this even
possible? I need to make this sheet as idiot-proof as possible which is
hard because I'm a pretty big idiot myself.

Sorry if this makes no sense but I'm far from an expert and he's
seriously driving me insane...


--
charlilot
------------------------------------------------------------------------
charlilot's Profile: http://www.hightechtalks.com/m791
View this thread: http://www.hightechtalks.com/t2339836

It would be easier to solve the source workbook problem. Create two folders,
one called something like "Current" and the other called something like
"Archive". Set up a naming convention so that the Current folder always
contains a file with a fixed name (eg JobData.xls, and the archive folder
contains all the old files, with the date in the name --
JobData2006_01_26.xls (or some such). Write out your procedure on a little
piece of paper and glue it to the side of the supervisor's screen.

Much less trouble than creating a macro that has to be run and maintained
for ever.






"charlilot" wrote in
message ...

We have one main workbook that the supervisor fills out that lists jobs,
times staff etc. I've just set up links to the daily worksheet workbooks
that automatically fill in which staff member is doing which jobs each
day.

But the problem is, the supervisor makes a copy and changes the name of
the main workbook weekly so that the sources for the other 7 workbooks
are now wrong.

I've shown him how to go editlinkschange source but he is
continuously forgetting how to do it and thinks this takes too long.
Does anyone know a macro that will automatically change the sources for
the other 7 workbooks using a command button on the main book? I began
designing a form which he can type the name of the new workbook from a
command button that simultaneously opens the 7 workbooks. But I have no
idea how to change source with information from a text box. Is this even
possible? I need to make this sheet as idiot-proof as possible which is
hard because I'm a pretty big idiot myself.

Sorry if this makes no sense but I'm far from an expert and he's
seriously driving me insane...


--
charlilot
------------------------------------------------------------------------
charlilot's Profile: http://www.hightechtalks.com/m791
View this thread: http://www.hightechtalks.com/t2339836