How can I stop cells recalculating after macro run

[n.kennedy927]

Hi folks

I have a macro which when run removes a couple of excess lines from a
report (produced from elsewhere). The excess lines appear depending on
what time of the day the report runs.

When I run the macro, the links on the second sheet are recalculating,
changing to the formula to an incorrect cell.

I have tried the following to get around this issue, without luck:-
All formulas in the linked sheet have been made absolute references
using the $ for all cell references;
Within the macro, made the calculation method Manual then reverting to
Automatic at the end of the macro.

The basic question after this waffle is this -
Can I get a macro to run, remove the excess lines from my raw report
but leave all of the existing formulas intact so they read from the
coorect cells.

Cheers for any help in advance

Neil

 

[Niek Otten]

Hi Neil,

If you use the INDIRECT() function to address the cell, the formulas will not change.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi folks
|
| I have a macro which when run removes a couple of excess lines from a
| report (produced from elsewhere). The excess lines appear depending on
| what time of the day the report runs.
|
| When I run the macro, the links on the second sheet are recalculating,
| changing to the formula to an incorrect cell.
|
| I have tried the following to get around this issue, without luck:-
| All formulas in the linked sheet have been made absolute references
| using the $ for all cell references;
| Within the macro, made the calculation method Manual then reverting to
| Automatic at the end of the macro.
|
| The basic question after this waffle is this -
| Can I get a macro to run, remove the excess lines from my raw report
| but leave all of the existing formulas intact so they read from the
| coorect cells.
|
| Cheers for any help in advance
|
| Neil