How to freeze screen position, without turning off screen updating, while macro runs?

Hi, apologies for such a basic question.

I have a macro which gathers results from various places in a large sheet and value-pastes them into a table.

To prevent the user from getting seasick while the macro runs, I know I can use the

application.screen updating = false

command, but this does a bit more than what I want.

I want the user to see the results appearing in the table, without subjecting him or her to a fast-motion view of the screen jumping around. So instead of turning updating off altogether, is there a way I can tell Excel to hold the current screen frame while the macro runs? The result should just look like a screen which is static, except that the results can be seen being added to the table.

I'd be grateful for any help.

The screen 'jumps' because you are selecting cells - there's no need to select cells to act on them cells so you could just leave the screen 'focused' on your results table and screen refresh on.

For example, some code:

Please Login or Register  to view this content.

Could be replaced by

Please Login or Register  to view this content.

Very rough example only - it would help if you posted your code...

Thank you cytop, you nailed it!

Sorry, but actually, now that I think about it, I'm not sure how to apply your advice.

Here's what I want to accomplish, and some of the code I'm using (which is the result of a recorded macro, using relative references). I apologise if this is a bit long; I'd rather be too clear than not clear enough.

I am gathering the "live" results of a model, under different scenarios, into a 3-column summary table. The results get pasted in as hard-coded values. (I could use a Data Table for this, but for various reasons don't want to).

The first column consists of years when I would stop a project. It is already contains values -- 2014, 2015 as well as 6 more succeeding years.

The second column needs to shows the corresponding profit if I stop in those years. The third column shows my corresponding tax charge if I stop in those years.

The table has 8 rows, as I want to summarise the profit and taxe charges for each of 8 years -- 2014, 2015, etc. Here is my code works for the first two years.


-- I go to the first row of the first column, which I have named "ResultsTable_first_calendar_year". It contains the value 2014. I copy this value and value paste it to a cell elsewhere on the sheet which determines the year when the "live" model stops the project. This cell is called "Year_to_stop_project". I.e., here, I am telling the model I want to stop in 2014.


Application.Goto Reference:="ResultsTable_first_calendar_year"
Selection.Copy
Application.Goto Reference:="Year_to_stop_project"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

-- This causes the model to calculate my profit and my tax charge if I stop in 2014. To collect these results in my table, I

a) copy the model's "live" resultant profit from a cell named "My_Profit"and value paste this into the first row in the second column of my table , i.e. into a cell which I have named ""ResultsTable_My_Profit_first_calendar_year"


Application.Goto Reference:="My_Profit"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="ResultsTable_My_Profit_first_calendar_year"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

and then b) copy the model's "live" resultant tax charge from a cell named "My_Tax charge"and value paste this into the first row in the third column of my table , i.e. into a cell which I have named "ResultsTable_MyTax_Charge_first_calendar_year"

Application.Goto Reference:="My_Tax_charge"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="ResultsTable_MyTax_Charge_first_calendar_year"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

So now the first row of the summry table is filled in. The first column (again, which was already filled in in adavance) contains 2014; the second column has, as a hard-coded value, my profit if I stop the project in 2014; and the third column has, as a hard-coded value, my tax charge if I stop the project in 2014.


Next I collect the results for when I stop the project in 2015. As the row in the table corresponding to 2015 is the second row, I do everything as before, expect that I guide the macro to the second row by first telling it to go to the same three named "ResultsTable..."cells for the first calendar year, but then to go down one row, using the ActiveCell.Offset(1, 0).Range("A1").Select command.



Application.Goto Reference:="ResultsTable_first_calendar_year"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="Year_to_stop_project"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.Goto Reference:="My_Profit"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="ResultsTable_My_Profit_first_calendar_year"
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.Goto Reference:="My_Tax_charge"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="ResultsTable_MyTax_Charge_first_calendar_year"
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


So now I have the second row filled in as well.


The macro continues in this vein. To fill in the third row, for 2016, I again tell the macro to go to the first row of the relavent cells of the results tabe, and then to go down two rows, using the ActiveCell.Offset(2, 0).Range("A1").Select command.

To fill in the fourth row, for 2017, I again tell the macro to go to the first row of the relavent cells of the results tabe, and then to down three rows, using the ActiveCell.Offset(3, 0).Range("A1").Select command.


So basically the macro jumps all over the place, setting new project stopping years, copying the model results, and pasting them into the appropraite rows of the summary table.

This works, but I realise it is very primitive. How can I get the values into the summary table (as hard-coded values) without having to "travel" around the sheet?

(By the way, I realise that this macro can be streamlined further by using some sort of loop procedure, but for various reasons I don't want to do that).

Again, any help would much appreciated.

Hmmm... Anyone?