Updating Worksheets

[ItIsHardToProgram]

I am looking for a macro that with update all links in the worksheet external and internal and that also calculates manualy all cellsé

I will put this macro in the onopen event of the workbook.

Of course I know how to manualy calculate all cell, then putting it back to automatic calculation, but I do not know or have not heard of a function to update al internal/external links.

Let me explain myself, if I copy a sheet with links at the end of the workbook, the links will appear as N/A I have to either replace something in the formula or click the formula and press enter for it to calculate.

This causes some inexperienced excel users to have worksheets that do not balance and waste alot of time trying to figure out why they don't balance.

Thanks alot all

Julien,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[macropod]

Hi Julien,

To update the external links, try:
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Be mindful, though, that some external references only work if the source workbook is open. So, if you're still getting #N/A errors after applying the above line, you'll probably need to open the workbooks concerned.

As for the internal links, these are all updated immediately the workbook recalculates. So, unless you've set the recalculation to manual, you don't need to do anything. If you are using manual recalculation, try:
Application.Calculate

Note: These examples came straight out of the Excel Help file. It's a good place to look first.

Cheers


[MS MVP - Word]

 

[ItIsHardToProgram]

Hey macropod,

The thing is we get weird behaviors, sometimes the links to, for example, a pivot table, or other cells in other sheets do not update even though we are in automatic recalculation.

I was hoping that this specific behavior could be fixed.

Thanks though, the external reference automaticaly ask for update on open,

Hanve a nice evening/weekend

Julien,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[macropod]

Hi Julien,

If individual cells are not recalculating correctly, it's a fair bet they've been formatted as text.

Cheers

[MS MVP - Word]

 

[Gavona]

sometimes the links to, for example, a pivot table, or other cells in other sheets do not update even though we are in automatic recalculation.

The actions described so far will not refresh pivot tables. That would need different code.

As Macropod said "Be mindful, though, that some external references only work if the source workbook is open. So, if you're still getting #N/A errors after applying the above line, you'll probably need to open the workbooks concerned."

How about adding code to open all linked workbooks (read only). Then recalculate, then close the linked books?
(This will not open workbooks that are the source of a pivot table in your current workbook.)

A reference to a named range in a closed workbook will evaluate if the definition of that range is 'normal'. The workbook needs to be open if the named range is dynamic (automatically adjusts as data is added, includes functions such as count in its definition). For this reason I tend to avoid dynamic ranges, instead relying on code such as:

Range("myData").CurrentRegion.Name = "myData"

Gavin

 

[ItIsHardToProgram]

For my part I try to avoid any link period, unless its in the same workbook, and I do not seem to be getting this problem.

The specific behavior reported was from other employees, that all had the same problems, Ive found part of the problems they were having, for once they had circular references disabling the calculation of certain sheets, and for the other part I haven't actualy seen the behavior, in all that they've shown me.

From this point on I will have to assume it was their mistake and not a specific excel behavior, thanks for your insights though.

I like to store data in variables, virtual tables etc... work with them through vbase and put them back after in excel.

This is not the best way to proceed, but its fun, when I build templates it depends on the type of templates.

Thanks all, I consider this thread done.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.