Excel set active sheet

Worksheet object (Excel)

Represents a worksheet.

Remarks

The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook.

The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

Example

Use Worksheets (index), where index is the worksheet index number or name, to return a single Worksheet object. The following example hides worksheet one in the active workbook.

The worksheet index number denotes the position of the worksheet on the workbook’s tab bar. Worksheets(1) is the first (leftmost) worksheet in the workbook, and Worksheets(Worksheets.Count) is the last one. All worksheets are included in the index count, even if they are hidden.

The worksheet name is shown on the tab for the worksheet. Use the Name property to set or return the worksheet name. The following example protects the scenarios on Sheet1.

When a worksheet is the active sheet, you can use the ActiveSheet property to refer to it. The following example uses the Activate method to activate Sheet1, sets the page orientation to landscape mode, and then prints the worksheet.

This example uses the BeforeDoubleClick event to open a specified set of files in Notepad. To use this example, your worksheet must contain the following data:

• Cell A1 must contain the names of the files to open, each separated by a comma and a space.
• Cell D1 must contain the path to where the Notepad files are located.
• Cell D2 must contain the path to where the Notepad program is located.
• Cell D3 must contain the file extension, without the period, for the Notepad files (txt).

When you double-click cell A1, the files specified in cell A1 are opened in Notepad.

Events

Methods

Properties

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник

VBA Select Sheet, Activate Sheet, and Get Activesheet

In this Article

This article will discuss the ActiveSheet object in VBA. It will also discuss how to activate, select, and go to Worksheets (& much more). Read our full VBA Worksheets Guide for more information about working with worksheets in VBA.

ActiveSheet

In VBA, ActiveSheet refers to the currently active Worksheet. Only one Sheet may be active at a time.

Activate Worksheet (Setting the ActiveSheet)

To set the ActiveSheet use Worksheet.Activate:

The Activate Sheet command will actually “go to” the sheet, changing the visible Sheet.

The above example uses the Sheet (Tab) name. Instead you can use the VBA code name for the worksheet:

ActiveSheet Name

To get the ActiveSheet Name:

Selected Sheets vs ActiveSheet

At any point in time, only one Sheet can be the ActiveSheet. However, multiple Worksheets can be selected at once.

When multiple Worksheets are selected only the “top-most” Worksheet is considered active (the ActiveSheet).

Select Worksheet

If you would like to select a worksheet instead of activating it. Use .Select instead.

Select Worksheet by Tab Name

This selects a Worksheet based on it’s Sheet Tab Name

Select Worksheet by Index Number

This selects a Worksheet based on it’s position relative to other tabs

Select Worksheet With VBA Code Name

Selecting worksheets by code name can prevent errors caused by worksheet name changes.

Select Current Worksheet

To select the current Worksheet, use the ActiveSheet object:

More Activate / Select Sheet Examples

Set ActiveSheet to Variable

This will assign the ActiveSheet to a Worksheet Object Variable.

Change ActiveSheet Name

With ActiveSheet

Using the With Statement allows you to streamline your code when working with objects (such as Sheets or ActiveSheet).

Notice how you don’t need to repeat “ActiveSheet” before each line of code. This can be a huge time saver when working with a long list of commands.

Loop Through Selected Sheets

The following macro will Loop through all selected sheets, displaying their names.

GoTo Next Sheet

This code will go to the next Sheet. If the ActiveSheet is the last Sheet, then it will go to the first Sheet in the Workbook.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник

VBA ActiveSheet – Excel Active Sheet Object

VBA Reference

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

• 

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ Project Management Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project Management Template

Ultimate Resource Management Template

Project Portfolio Management Templates

VBA ActiveSheet Object helps to refer the currently activated sheet in the active workbook. We can get all properties and methods of the ActiveSheet using VBA. Let us see the useful of the examples on Excel VBA ActiveSheet Object.

What is Active Sheet in Excel VBA?

Worksheet which is currently activated in the Active Workbook and Active Window is referred as Active Sheet. You can make any Worksheet as Active Worksheet by Activating a Worksheet. You can use Activate Method of Worksheet to activate a sheet using Excel VBA.

Set a Worksheet as Active Sheet in VBA

You can use Activate method to set worksheet as active sheet in VBA. For example, you can set the Sheet 3 as active sheet by calling the Activate Method.

Reading the Data from ActiveSheet

We can use the VBA to read the data from ActiveSheet Object. We can use all the methods and properties of a worksheet Object. For example, the following example get the data from ActiveSheet and show it in a message box.

The code statement ActiveSheet.Range(“D5”) is reading the data from Range D5 of ActiveSheet. It is not mandatory to specify the ActiveSheet befor Range object in the above macro. Range(“D5”) also refer the data from ActiveSheet.

Then, when do we use ActiveSheet Object in Real-time projects?

Uses of ActiveSheet in VBA Development

We deal with multiple worksheets while automating an Excel Task. We can simply set the ActiveSheet to a variable and keep it fro future reference.

For example: Let us say, we have 3 worksheets in the Workbook and currently you have activated Sheet 2 and You wants to move into Sheet 3 and refer the Sheet2 Data. You can use the ActiveSheet Object in this scenario.

Get the Name of the ActiveSheet

You can use the .Name property of the ActiveSheet object to return the ActiveSheet Name using VBA.

Copy the Data from Other Sheet and Paste in the ActiveSheet

We can copy the data from a worksheet (it can be the same sheet or a different sheet) and paste in the ActiveSheet.

The above macro will Activate the Sheet 2. And copy the data from Sheet 3 and Paste at Range G1 of the ActiveSheet.

Count Shapes in ActiveSheet

The following macro will return the number of shapes in ActiveSheet.

Count Charts in ActiveSheet

The following macro will return the number of Chart Objects in ActiveSheet.

Protect in ActiveSheet using VBA

You can use the Protect Method of ActiveSheet to password protect the Sheet.

UnProtect: You can use unprotect method of Activesheet to unprotect the sheet.

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Источник

Active Sheet & Select Sheet in Excel VBA

In this post, you’ll be learning how you can select a worksheet and activate it using Excel VBA.

ActiveSheet in Excel

The term ActiveSheet, refers to the currently active Worksheet in Excel VBA.

Let’s see in detail about the active sheet command.

To run the code,

• Under the developer tab, click visual basic

• Click the insert option and choose module
• Enter your code and click run.

How to Activate or Set the WorkSheet in Excel VBA?

To set the ActiveSheet use Worksheet.Activate:

This sheet will activate a new worksheet in the current workbook.

If you want to activate an existing sheet,

ActiveSheet Name

To get the ActiveSheet Name, use the property ActiveSheet.name in Excel VBA.

Selected Sheets vs ActiveSheet in Excel VBA

At any point in time, only one Sheet can be the ActiveSheet in Excel VBA.But, multiple Worksheets can be selected at once.

If multiple Worksheets are selected, then the Worksheet with top most priority is considered as active sheet.

Select Worksheet

If you want to select a worksheet instead of activating it you can use .select in the above codes instead of activate command.

Select Worksheet by Tab Name

This code line selects a Worksheet based on its Sheet Tab Name

Select Worksheet by Index Number

This code line selects a Worksheet based on its position relative to other tabs

Select Worksheet with VBA Code Name

To select worksheet using VBA code name, enter the following code

Selecting worksheets by code name will prevent errors.

Select Current Worksheet

To select the current Worksheet, you can use the ActiveSheet object:

Change ActiveSheet Name

To change the active sheet name

GoTo Next Sheet

To go to next sheet, enter the following code. If the active sheet is the last sheet, this command will go to the first sheet.

Источник

VBA Activate Worksheet in Excel

VBA Reference

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

• 

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ Project Management Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project Management Template

Ultimate Resource Management Template

Project Portfolio Management Templates

VBA Activate Worksheet method is used to makes the current sheet as active sheet. Here we are the examples using Activate method of worksheet object in VBA. It is very frequently used method while writing VBA macros. We also see the VBA ActiveSheet object with examples.

When we need to use Activate Worksheet method in VBA?

We use Activate worksheet method to activate current sheet as active sheet. When we are working with multiple sheets in a workbook, if we want to move or go to another sheet in the same workbook or another workbook we use activate worksheet method.

VBA Activate Worksheet Method- Syntax

Here is the example syntax to activate Worksheet using VBA. You can use either a Worksheet name or Worksheet number. Always best practice is to use sheet name.

Worksheets(“Your Worksheet Name”).Activate
‘Or
Worksheets(“Worksheet Number”).Activate

Where Activate is the method of Workbook object is used to makes current sheet as active sheet.

VBA Activate Worksheet – with Name: Example 1

Please see the below VBA codes to activate Worksheet. In this example we are activating a Worksheet named “Project1”.

VBA Activate Worksheet Method– with Number: Example 2

Please see the below VBA code or macro procedure to activate Worksheet. In this example we are activating first Worksheet in the active workbook.

VBA ActiveSheet Object

We can refer the currently activated worksheet using Excel VBA ActiveSheet object. ActiveSheet VBA object is very usefull while automating tasks and working on currently active sheet in the active workbook window. If you ignore the ActiveSheet object while refering any other object like range or chart, VBA will treat the ActiveSheet as the current sheet by default. For example: Following are the two macro statements both will refer the active sheet.

both the above statements print some value at Range A1 of Activesheet.

Set ActiveSheet in Excel VBA

It is very convinient refer the Active Sheet in Excel VBA by setting into a Variable. We can assign and set ActiveSheet to an object and refer at any place of the procedure. Here is the syntax to Set ActiveSheet in VBA.

This code will print the some value at Range A1 of Activesheet.

VBA Activate Worksheet Method- Best Approach

Note: Always better to use the Worksheet name, instead of Worksheet number. The best is to assign the Worksheet to an object and then do whatever task you want to do with that particular Worksheet object.

When working with multiple Worksheets, you should refer the Worksheet with exact Worksheet name to correctly update your data into target Worksheet. Create Worksheet object and refer the Worksheet with the object whenever you require.

Let us see another example to understand the accessing the Worksheets using objects. You do not need to activate Worksheet to deal with any Worksheet.

VBA Activate Worksheet Method- Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:

1. Open an Excel Worksheet
2. Press Alt+F11 :This will Open the VBA Editor. Otherwise, you can open it from the Developer Tab
3. Insert a Module from Insert Menu
4. Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
5. Save the file as macro enabled Worksheet
6. Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and check how the sheet is activating.

Related Resource

You have seen how to activate worksheets using Excel VBA and referring the AciveSheet. Following are the example macros to Activate Excel Workbook and Range.

Источник