VBA basics
27. Select book / sheet (Select, Activate)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

27. Select book / sheet (Select, Activate)

Some of this topic was processed by machine translation. Original


Excel handles multiple books,
Each book has multiple sheets.
Therefore, it is necessary to handle multiple books and multiple sheets even in macro VBA.


In VBA so far, the active sheet of the active book has been targeted.
Therefore, I will explain VBA for switching fictional books and active sheets.
The first thing that must be explained is the meaning of the word "active".

Active

Active book, active sheet, active cell
What are these points to?
That is what is now the target of operation.
A cell in a book sheet that is entered when you type a letter and press Enter.
That is the active cell in the active sheet of the active book.

It is different from simply selecting it.

You can make multiple selections, hold down Ctrl and select while holding down Shift.
Multiple selections are possible.
Therefore, there are multiple "selections", but "active" is always one.

VBA macro book sheet select active

In this case, the selection sheets are Sheet1 and Sheet2,
The active sheet is Sheet1.

You cannot select multiple books.

There is only one active book.

ActiveWorkbook
This is how to specify an active book in a macro.
ActiveSheet
This is how to specify the active sheet in a macro.

Activate book

To activate a specified book with macro VBA:
Workbooks ("book name"). Activate
Write like this.
The book name must also specify the extension (.xls, .xlsx, .xlsm).

In macro recording,
Windows ("book name"). Activate
It is recorded like this.

The above two differ when multiple windows are opened for one book.
Originally it is Windows ("window name"),
If there is only one window for the book,
Window name = Book name
It will be that.

Sheet selection, sheet active

Sheet selection
To select a specified sheet with macro VBA,
Sheets ("Sheet name"). Select
Or
Sheets (Index) .Select

The index is the numerical value of the sheet order.
If there is no non-display sheet, it will be 1,2,3 ... in order from the leftmost sheet.
If there is only a worksheet, the same applies to Sheets and WorkSheets.

Activate sheet
To select a specified sheet with macro VBA,
Sheets ("Sheet name"). Activate
Or
Sheets (index) .Activate

Even in Actovate, the sheet is selected.

Then
The difference between Sheets ("sheet name"). Select and Sheets ("sheet name"). Activate is

If you select only one sheet, it's exactly the same thing.
You can write either.
The difference appears when multiple sheets are selected (work grouping).
When only one sheet is selected
Selection sheet = Active sheet
It works the same with Select and Activate.
When multiple sheets are selected
In Select,
Only the specified sheet is selected, and the specified sheet becomes active.

In Activate,
The selected sheet becomes active without changing the selected sheet.

Multiple sheet selection (work grouping)

Sheets (Array ("Sheet name 1", "Sheet name 2", ...)). Select
This selects multiple sheets.

Array is an array.
And in this state,
Sheets ("Sheet name 2"). Activate

In this case, only the active sheet is moved without changing the multiple sheet selection.
But in this state,
Sheets ("Sheet name 2"). Select
If selected, the work group is canceled and sheet name 2 is selected and activated.

However, the operation of grouping sheets in a macro is not performed.
It is better not to do it than to say not.

If you want to perform the same process on multiple sheets in a macro,
This means that it is better to perform the same process on multiple sheets in a repetitive process rather than a work group.
Do the same for all sheets (For ~ Worksheets.Count)

Sheet selection when multiple books are open

Sheets ("Sheet name"). Select
You can only select sheets in the active book.
If you want to select a sheet other than the active book,
Activate the workbook, then execute the sheet's Select method.

When Book2.xlsx is inactive and you want to select Book2.xlsx Sheet2,
Workbooks ("Book2.xlsx"). Activate
Sheets ("Sheet2"). Select
In this way, activate the workbook → sheet.

However, you can also write in one line using the Activate method.
Workbooks ("Book2.xlsx"). Sheets ("Sheet2"). Activate
You can still select Sheet2 in Book2.
There is no reason to write in a single line, so it may not be used much.



Articles with the same theme "VBA basics"

24. Input box (InputBox function)
25. About named arguments
26. General practice question 2
27. Select book / sheet (Select, Activate)
28. Select cell / row / column (Select, Activate)
29. Delete / Insert cell / row / column (Delete, Insert)
30. General practice question 3
31. Format Cells (display format, NumberFormatLocal)
32. Format Cells (Alignment)
33. Format Cells (Font)
34. Format Cells (fill, interior)



  • >
  • >
  • 27. Select book / sheet (Select, Activate)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text