There are times when the user shouldn't see lots of screen activity while a macro is running. After all, macros are supposed to work magically! Screen updating can be distracting, plus it slows down the macro. Here's a couple of ways to solve the problem. First, there's no need to activate a sheet to work with its objects if the code provides an explicit reference to them. For example, the first line in the following code selects objects in GTempSheet without activating it so the user does not see the changes while the code is running.
Worksheets(GTempSheet).Range("A1").Select '<--Explicit reference so
' GTempSheet doesn't have to be activated.
LastRow = Selection.CurrentRegion.Cells.Count
If Gheader = xlNo Then
Selection.Range(Cells(2, 1), Cells(LastRow, 1)).Name = "Data1"
Else
Selection.Range(Cells(1, 1), Cells(LastRow, 1)).Name = "Data1"
End If
The above code example also illustrates using the Count property to count the cells in a range and then adjust the range to include or exclude the first cell.
The second way to control screen updating is to turn it off with the Application.ScreenUpdating property. Here's code that does this:
Application.ScreenUpdating = False
'Sort the data allowing for a label in first cell if needed.
Selection.Sort key1:="Data1", order1:=xlAscending, header:=xlYes, Orientation:=xlTopToBottom
Set this property to False to turn screen updating off, then set it back to True when it should happen again. The macro will run faster, but it will be important to test the effects of turning screen updating off before going crazy with it. If a dialog box is visible, the user may want to move the dialog to see a cell behind it. With updating turned off, all they will get is a blank hole staring at them. Usually the best philosophy is to turn updating off only when absolutely needed, and the rest of the time try to use explicit references to objects the user doesn't need to see.