Changing source on multiple workbooks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

charlilot - 23 Jan 2006 04:47 GMT

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 workbook
that automatically fill in which staff member is doing which jobs eac
day.

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

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

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

--
charlilo

Reply to this Message

Jezebel - 26 Jan 2006 21:08 GMT

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.

> 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
[quoted text clipped - 17 lines]
> Sorry if this makes no sense but I'm far from an expert and he's
> seriously driving me insane...

Reply to this Message