What is the difference between Sheets.Select and Sheets.Activate?
The difference is in their flexibility.
Activate make the specified sheet the active sheet, and may only be applied to a single sheet
Select allow for optionally extending the currently selected sheets to include the specified sheet, eg
and also allow for selecting an array of sheets
In their minimal form
Activate do the same thing.
For example, if only one sheet is currently selected (say
Sheet3) or if more than one sheet is selected but excluding say
Worksheets("Sheet2").Activate both make
Sheet2 the sole selected and active sheet.
On the other hand, if say both
Sheet3 are selected and
Sheet2 is the active sheet, then
Worksheets("Sheet3").Activate leaves both sheets selected and makes
Sheet3 the active sheet, whereas
Sheet3 the sole selected and active sheet.
To expand on the above: When the code below is run with Replace:=False no worksheet deactivation event occurs on sheet4. If Replace:=True is used instead then the de-activation event will fire.
Preventing the event is desirable in most circumstances as it can cause unexpected behaviour.
This means that select is only the equivalent of CTRL+Clicking a worksheet tab IF replace:=false is used.
Dim rng As RangeSheet4.Select Replace:=FalseSet rng = SelectionSheet5.Select Replace:=TrueSelection = rng.Value
Thanks for your posts as it helped me understand the difference.