What is the difference between Sheets.Select and Sheets.Activate?

excel spreadsheet vba
Mehper C. Palavuzlar picture Mehper C. Palavuzlar · Apr 10, 2013 · Viewed 87.4k times · Source

In VBA for Excel, what is the difference between Sheets.Select and Sheets.Activate ?

Answer

chris neilsen picture chris neilsen · Apr 10, 2013

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

Worksheets("Sheet2").Select Replace:=False

and also allow for selecting an array of sheets

Sheets(Array("Sheet3", "Sheet2")).Select

In their minimal form Select and 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 Sheet2, then Worksheets("Sheet2").Select and Worksheets("Sheet2").Activate both make Sheet2 the sole selected and active sheet.

On the other hand, if say both Sheet2 and Sheet3 are selected and Sheet2 is the active sheet, then Worksheets("Sheet3").Activate leaves both sheets selected and makes Sheet3 the active sheet, whereas Worksheets("Sheet2").Select makes Sheet3 the sole selected and active sheet.

Related questions

Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

We need to write an Excel spreadsheet with VBA code in it; the code reads and performs operations on the data in the first worksheet. The user will be receiving spreadsheets containing data but that do not contain the VBA …

Read More
Selecting non-blank cells in Excel with VBA

I'm just beginning to dive into VBA and I've hit a bit of a roadblock. I have a sheet with 50+ columns, 900+ rows of data. I need to reformat about 10 of those columns and stick them in a new workbook. How …

Read More
Excel VBA code to select non empty cells

In Excel, let's I have data in B2 to B7 and C2 to C7 . In VBA I can write a macro to select it: Sub Macro1() Range("B2:C7").Select End Sub How do I rewrite the code so that …

Read More