How to keep view still when running macos?

[Ace70]

Hi,

This is a probably a very basic quetsion, but how do I ensure that
when a macro is run, that my current view of the worksheet on the
screen remains unaffected and doesn't jump around trying to follow the
locus of the macro as it does its stuff perhaps in areas of the
worksheet not currently in my original field of view?

Cheers

Ace70

 

[Guest]

Try:

Application.screenupdating = False

your macro

Application.screenupdating = True

Mike

 

[Ace70]

Awesome. Thanks!

 

[Ace70]

I may have jumped the gun here a bit. If I do as suggested:

Application.screenupdating = False

your macro

Application.screenupdating = True

....then unless my current view is at the topmost/left most part of the
worksheet, executing the macro WILL change my current view to be end
up being at the topmost/leftmost part of the worksheet when the macro
completes. What shoulld do to prevent this so that the current view
remains still/constant regardless of where I am on the worksheet when
I execute the macro?

Ace70

 

[Andrew Taylor]

I may have jumped the gun here a bit. If I do as suggested:




...then unless my current view is at the topmost/left most part of the
worksheet, executing the macro WILL change my current view to be end
up being at the topmost/leftmost part of the worksheet when the macro
completes. What shoulld do to prevent this so that the current view
remains still/constant regardless of where I am on the worksheet when
I execute the macro?

Ace70

As a general rule you should try to avoid changing the selection in a
macro: it's almost always unnecessary, can make the macro run slower,
and (as you mention above) forgets your initial state. So for
example
instead of

Workbooks("NewBook.xls").Select
Worksheets("SomeSheet").Select
Range("A1:C20").Select
Selection.Font.Bold = True

(as typically generated when you record a macro) you can use :

Workbooks("NewBook.xls").
Worksheets("SomeSheet").Range("A1:C20").Font.Bold = True

You can do this more elegantly using varaiables to keep track of what
you're working on:

Dim wb as Workbook, ws as Worksheet, rangeToBolden as Range
Set wb = Workbooks ("NewBook.xls")
Set ws = wb.Worksheets("SomeSheet")
Set rangeToBolden = ws.Range ("A1:C20")
rangeToBolden.Font.Bold = True
' could do other stuff with ws here..

If you absolutely _have_ to move away from you initial selection,
then you can remember it at the start of the macro and
restore it at the end:

dim wbInitial as Workbook , wsInitial as Worksheet, rngInitial as
Range

' save initial state
Set wbInitial = activeworkbook
Set wsInitial = activesheet
Set rngInitial = selection
'
'' macro code here
'
'restore initial state
wbInitial.Activate
wsInitial.Select
rngInitial.Select

 

[Ace70]

Hi, thanks for the general rule tip.
All my macros deal with stuff on the one worksheet. They do things
such as refresh pivot tables, sort lists and copy cells from one spot
to another.
I have tried going into the VBA code for the macros I have that jump
around and have tried to apply the same rules as you did to your
example. However I did get errors I don't kno whow to correct.

eg. I initially started off with:

Sub Sort_New_List()

Range("B53133").Select
Selection.Sort Key1:=Range("L54"), Order1:=xlDescending,
Key2:=Range( _
"M54"), Order2:=xlDescending, Key3:=Range("N54"),
Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Range("B53").Select
' I originally selected B53 as where I want the window view to be
viewing when the macro is complete

End Sub

From this, I tried deleting the "Select" cases like in the example

provided and I ended up with:

Sub Sort_New_List()

Range ("B53133")

Sort Key1:=Range("L54"), Order1:=xlDescending, Key2:=Range( _
"M54"), Order2:=xlDescending, Key3:=Range("N54"),
Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

End Sub

However I get an complie error invalid use of proptery where its says
Range ("B53133")

Any clues of tweaking this.

 

[Dave Peterson]

I think you dropped a "with" keyword.

Option Explicit
Sub Sort_New_List()
with worksheets("sheet9999") '<-- change this
'or with Activesheet '?????
with .Range("B53133")
'column L is the 11th column in B
'column M is the 12th column in B
'column N is the 13th column in B
.sort key1:=.columns(11), order1:=xldescending, _
Key2:=.columns(12), Order2:=xlDescending, _
Key3:=.columns(13), Order3:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
end with
end with
End Sub

(Uncompiled and untested--watch for typos.)

And I bet you know if there are headers or not. If you do, don't let excel
guess. Supply the information yourself and you won't have to worry about excel
guessing wrong.