Solved: Select vs. Activate

[wadirks]

I am unclear on the differences between select and activate in the context of worksheet or range or cell. The below code snippet uses select. Visually it is distracting to see things flash around if you are doing selects in several different places in sequence. Can I use activate and elimiate the visual effect of select? Or is there another way without select to do the same thing?

Please do concentrate on what the code does as the visual effect the code creates. I also want to apply your suggestions on other macros.

[VBA] Sheets("S Rpt").Select
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.RowHeight = 21
'
' copy "task" column format
'
Range("H3").Select
lSRptFirstRow = Selection.Row
Selection.End(xlDown).Select
lSRptLastRow = Selection.Row
'
Range("H3").Select
Range(Selection, Cells(lSRptLastRow, Selection.Column)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(3, lSRptTaskCol).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'
' copy "call" column format
'
Range("J3").Select
Range(Selection, Cells(lSRptLastRow, Selection.Column)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(3, lSRptCallCol).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'
' copy "start" column format
'
Range("L3").Select
Range(Selection, Cells(lSRptLastRow, Selection.Column)).Select
Application.CutCopyMode = False
Selection.Copy
Cells(3, lSRptStartCol).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False[/VBA]

[OBP]

To stop the visual effects put
Application.ScreenUpdating = False
and then when it has finished the code put in
Application.ScreenUpdating = True.

[Norie]

You don't actually need select/activate.

This code:
[vba]
Sheets("S Rpt").Select
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.RowHeight = 21[/vba]
Can be replaced with this.
[vba]
With Sheets("S Rpt").Range("F3")
.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
.RowHeight = 21
End With[/vba]

[Zack Barresse]

In plain English, you can Select multiple cells, but you can only Activate one at a time; the same goes for worksheets. That's the jist of it.


As for your code, you might be able to use something like this (untested) ...

[VBA]Sub test_me()
Dim lSRptFirstRow As Long, lSRptLastRow As Long
Dim lSRptTaskCol As Long, lSRptCallCol As Long, lSRptStartCol As Long
With Sheets("S Rpt")
.Range("F3").PasteSpecial xlPasteFormats
.Range("F3").RowHeight = 21
lSRptFirstRow = 3
lSRptLastRow = .Range("F3").End(xlDown).Row
.Range(.Range("H3"), .Cells(lSRptLastRow, 8)).Copy
.Cells(3, lSRptTaskCol).PasteSpecial xlPasteFormats
.Range(.Range("J3"), .Cells(lSRptLastRow, 10)).Copy
.Cells(3, lSRptCallCol).PasteSpecial xlPasteFormats
.Range(.Range("L3"), .Cells(lSRptLastRow, 12)).Copy
.Cells(3, lSRptStartCol).PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False
End Sub[/VBA]

[Bob Phillips]

Also, when you select a range of cells or group of worksheets, the first cell/worksheet selected becomes the active one.

[wadirks]

As always the advice has been good, thank you.