Updating Spreadsheets

[baggarwal]

Hi There:

I have Vlookup formulas in one master spreadsheet that look for values in other spreadsheets contained in other files.

However, the master file I work from does not calculate or update the cells automatically when someone makes a change to the file that Vlookup command is trying to find.

Instead I have to open up each file individually in order to get the new numbers reflected in the master file.

How can I get the changes reflected in the master file without individually opening each and every supporting file that feeds it with numbers?

 

[DonkeyOte]

would you be happy to automate the opening procedure (or do you do that anyway?). Thereby not having to physically do anything yourself.

e.g

Application.ScreenUpdating = False

Workbooks.Open("C:blah.xls")

Windows("Master.xls").activate
Sheets("calculating sheet").select
ActiveWorksheet.Calculate

Windows("Blah.xls").close savechanges = true

etc...

If you set this as an Autpen() Macro then this would take place without you needing to do anything except open the Master File.

If you

 

[baggarwal]

Hi There:

I tried the following code:
Application.ScreenUpdating = False

Workbooks.Open ("C:\book2.xls")

Windows("C:\Master.xls").Activate
Sheets("calculating sheet").Select
ActiveWorksheet.Calculate

Windows("C:\book2.xls").Close savechanges = True

It said Invalaid outside procedure on the first line.

Can you please send me the full code with the file locations I provided above.

Thanks again for your great support,

BA

 

[Chris Davison]

you shouldn't have to do this..... they should update automatically

is your calculation set to manual ?

(tools, options, calculation)

also, check out your links (edit, links) : there's a section at the bottom that specifies if the link is automatic or manual - check that they're all automatic

also, if your workbooks are being amended whilst you have your master open, you should be able to just hit F9 to force a recalc on any vlookups from external scources

otherwise (I'd suggest) there's something more seriously wrong - you shouldn't have to be opening individual files like are doing...