Updating Spreadsheets

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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 Auto_Open() Macro then this would take place without you needing to do anything except open the Master File.

If you
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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...

:(
 

Forum statistics

Threads
1,143,916
Messages
5,721,525
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top