Activate Sheet Using VBA in Excel (Worksheet.Activate)

When you have multiple worksheets in a workbook, you can manually activate a worksheet so that you see the content in that worksheet and can work in it.

The same thing can also be done with VBA using the Worksheets.Activate method.

In this article, I will explain how Worksheets.Activate method works and some examples of using VBA to activate worksheets.

This Tutorial Covers:

Worksheet.Activate Method

Worksheet.Activate is a method that allows you to activate the specified worksheet.

To use this method, you need to refer to the worksheet that you want to activate. This reference can be done by using the sheet name or the sheet number.

Activate Sheet By Name

Below is the VBA code that will activate Sheet1

When you run this code, it will activate Sheet1 of the current workbook.

Note that in this code, I have used Worksheets(“Sheet1”), as Sheet1 is a part of the Worksheets collection. You can also use Sheets(“Sheet1”) as it’s also a part of the Sheets collection.

Activate Sheet By Number

You can also activate sheets by their index number, which is it’s position in the workbook.

To activate the first sheet in the workbook, you can use the below VBA macro code:

In most cases, It is better to use the sheet name instead of the sheet number, as the sheet number can change if you change the position of the worksheet in the workbook.

Activate Sheet Based on Cell Value

Below is the VBA code that activates the sheet based on the value in cell A1.

The above code first fetches the name that is there in cell a1 in Sheet1 and uses this name in the Worksheets object, and then activates it.

You can simplify the above code by using a variable to store the name in cell A1 and then use it to activate that worksheet (as shown below).

Activate Sheet in When Opening the Workbook

If you always want a specific worksheet to be activated whenever you open a workbook, you will have to use the Workbook_Open event.

This event is going to trigger your VBA code whenever a workbook is open, and within the code, you can specify that it should always activate the specified worksheet.

Below is the VBA code that would always activate the Summary worksheet whenever the workbook (which has this code) is opened.

Note that the name of the above subroutine is Sub Workbook_Open() , which makes it a workbook open event and would execute the code in this subroutine whenever the workbook is opened.

An event code is not placed in the standard module. It needs to be placed on the object responsible for that event.

In this example, since we want the code to be run whenever the workbook is opened, we will have to place this code in the workbook object .

Here are the steps to put this code in the workbook object:

  • Press ALT + F11 to open the VBA Editor.
  • In the Project Explorer window on the left, double-click on “ ThisWorkbook ” object under the workbook for which you want this to happen.
  • In the code window that appears, paste the above code.
  • Save and close the VBA Editor.

Now, whenever you open the workbook in which we placed the code, it will trigger the Workbook_Open event and activate the Summary sheet.

Activate Sheet in Another Workbook

Below is the VBA code that would activate a worksheet named “ Summary ” in the already open Example.xlsx workbook.

Note that the above code will give you an error if the name of the workbook or the worksheet you want to refer to is incorrect or if the workbook is not already open.

If the workbook is closed, you can open it programmatically by modifying the code by including a line to open that workbook .

VBA Activate Sheet Not Working – Possible Reason

Here are some possible reasons you may be seeing an error when you’re trying to activate a sheet using VBA:

  • Sheet Does Not Exist : If you’re trying to activate a sheet that does not exist or if you have misspelled the name of an existing sheet that you’re trying to activate, you will see a Run time Error 9 – Subscript out of range error.
  • Workbook is Not Open : If you are trying to activate a sheet in another workbook that is not already open, you will see an error.
  • Sheet is Hidden or Very Hidden: If you’re trying to activate a sheet that is hidden or very hidden, you will get an error.
  • Event Handling : There might be some event-driven macros (like Worksheet_Activate or Workbook_SheetActivate ) that are overriding or interfering with the sheet activation.

It’s always a good idea to handle potential errors using error handling techniques like On Error Resume Next and On Error GoTo , especially when dealing with objects that might not always be present or available. This can provide more graceful feedback to users and help diagnose issues.

Select vs. Activate in VBA

Since we’re talking about activating worksheets using VBA, I think it’s important I also tell you the difference between the Select method and the Activate method in VBA.

While both of these methods may seem to be doing the same thing, here is the difference:

  • You can select more than one object (such as a sheet, range, or chart) using the Select method.
  • You can only activate one object using the Activate method.

Important Note : Most of the time, there is no need for you to activate a worksheet to do anything in it using VBA. Almost everything that you need to do can also be done without activating the worksheet. For example, if you want to enter values in a range, clear a range, apply filters, insert a chart, or anything else, you can do that without activating the sheet. Most of the times, activating a sheet is unnecessary, and sometimes even a bad idea.

What is VBA in Excel? Learn Excel VBA Programming!

Other Excel VBA articles you may also like:

  • VBA Copy Sheet to New/Existing Workbook
  • VBA Delete Sheet
  • VBA Check IF Sheet Exists
  • VBA Rename Sheet in Excel
  • Using Active Cell in VBA in Excel (Examples)
  • VBA Create New Sheet (Sheets.Add)

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Excel Tutorials

  • Excel Tutorials

Tips, Solutions, Tricks

Excel/VBA Active sheet: Learn to Access, Rename, Activate with 4 Examples

In this tutorial, how to make a non-active sheet in Excel an active sheet by using VBA code.

Working with Excel Active Worksheet using VBA

In this tutorial, you will learn how to make a non-active sheet in Excel an active sheet by using VBA code.

The examples will cover how to access an active sheet name, rename an active sheet, and make a sheet active and removing as well.

For the information, if a sheet is active, the actions you perform like macro actions, accessing data, etc. without referring to a sheet name/number will be performed on that active sheet.

An example of getting the name of Active Sheet in VBA

Let us start with a simple example of getting the active sheet name and displaying it in a message box.

Our sample Workbook has the following sheets:

VBA-activesheet-sample

In VBA, the Application object has a property ActiveSheet which enables us to work with the active sheet.

The ActiveSheet has a method called “ Name ” that can be used to get the active sheet name.

Display active sheet name in alert example

Place a command button in a sheet:

VBA-activesheet-name

Double-click on the button in “ Design Mode ” to open the VBA editor

Write this line of code in the click event of the button:

MsgBox "Active Sheet Name = " & ActiveSheet.Name

Save the code and get back to the sheet containing that button.

Click on the button after unselecting “Design Mode” and it should display our active sheet name in an alert as shown below:

how to make worksheet active vba

Making a sheet active in VBA example

To make a sheet active in VBA, you may use the Worksheet object with its Activate method.

Suppose, we were on the “test1” sheet in our sample Workbook. We want to make “Sheet5” active. This is how you can do it:

Worksheets("Sheet5").Activate

Let us have a look at this visually.

We placed a command button on the “test1” sheet.

VBA-activate-sheet

Double-click the button to open the VBA editor and write these lines of code on the click event of button:

Save the code.

Remember to Select “Design Mode” to open the editor.

Click on the button and it should display two message boxes. First, the current active sheet name i.e. “test1” in our sample sheet.

Then this line of code executes:

And again a message box displays that shows the following:

VBA-activated

Renaming the Active sheet example

For renaming the active sheet, simply use the ActiveSheet.Name i.e.

ActiveSheet.Name = "New Name"

To demonstrate that, consider our sample Workbook shown in the first example, let us change the name of the active sheet.

“Sheet5” is our active sheet and we want to rename it to “Product Information”. See below how.

Place a command button in Sheet5 and open the VBA editor by double-clicking on the button.

VBA-sheet-name

Write this code on the click event of the button:

Click the button and it will change the Sheet5 name as shown below:

VBA-sheet-name-changed

Removing the Active sheet example

Removing the Active sheet is also simple. Just use:

ActiveSheet.Delete

method and active sheet will be removed.

For example, we wrote this code line to the command button click event. As you click the button, Excel will trigger a warning message.

Pressing Delete will remove the active sheet, in our case “Product Information” sheet as shown below:

VBA-delete-active-shee

4 Examples to Learn Excel / VBA DateDiff Function

Len-lead-trail-spaces

How to Get Length of String by VBA Len function?

how to make worksheet active vba

How to Close Excel Workbook using VBA Code

You may have missed.

Excel-usage-jobs

MS Excel Statistics – Usage by Job, Country, Industry & App Downloads

vba-workbook-add-shee

How to Create New Excel Workbook using VBA

How to Activate Worksheet in Excel in VBA

  • How to Activate Worksheet in Excel in …

How to Activate Worksheet in Excel in VBA

This article helps us understand how to activate Worksheet in Excel in VBA with the help of some examples.

Activate Worksheet in Excel in VBA

When working in VBA with Excel files, there are multiple sheets. If we want to run the same process or function on all the sheets in single or multiple Excel files, we need to make another sheet active before running any function.

It takes time to write code for each sheet separately again and again.

For this purpose, VBA provides a method, Activate , that can be used to make any sheet active while working on Excel.

Let’s go through an example in which we will use VBA activate worksheet method to make the sheet an active sheet. This method is widely used when we write VBA macros.

We can use either the name or the number of the Worksheet. The best way is to use the sheet name.

This will make the Sheet2 or the second sheet in the Excel file an active worksheet.

ActiveSheet Object in VBA

Imagine if our current sheet is active or if we have activated another sheet in our code with the help of the Activate method. We want to mention the currently active sheet to perform some tasks.

We can easily mention it with the help of an object that is specifically made for the active sheet as ActiveSheet .

If we are working on graphs or charts and have forgotten which sheet is active right now, we can use the following code to directly mention the currently active sheet in VBA. We can use two options to perform the function on the active sheet, as shown below.

ActiveSheet Object In VBA

As you can see from the above code, we have used two methods, and both gave the same result because it doesn’t matter whether we mention the active sheet.

Once we set any sheet as an active sheet, the next code will automatically be implemented on the same sheet.

Set ActiveSheet in VBA

Now, let’s discuss setting the ActiveSheet in VBA. It is helpful to mention the Active Sheet in Excel VBA by setting it into a variable.

We can set and assign an active sheet to an object and use that object to mention the active sheet where ever we want. We can mention it at any place during the procedure.

The below code will print some value at range B1 of the Active Sheet.

Set ActiveSheet In VBA

Related Article - VBA Worksheet

  • How to Set Worksheets in VBA

TeachExcel

  • Excel Courses
  • Free Excel Courses
  • Excel Video Tutorials
  • Excel Keyboard Shortcuts

Premium Excel Course Now Available!

Build professional - unbreakable - forms in excel, 45 tutorials - 5+ hours - downloadable excel files, activate or navigate to a worksheet using macros vba in excel, black friday sale (65%-80% off).

Excel Courses Online Video Lessons Excel Guides

VBA Course (80% Off)

Forms Course (65% Off)

Email Course (65% Off)

Make a particular worksheet visible using a macro in Excel.

This is called activating a worksheet and is rather easy to do.

Activate Worksheet by Name

Activate worksheet by index number.

Let's navigate to, activate, the worksheet called "Sheet2".

08008f3433d614b4eb713fdcde4e01eb.jpg

Worksheets("Shee2") is how we reference the sheet to which we want to navigate.

Sheet2 is the name of the sheet to which we want to navigate. Make sure to surround it with double quotation marks.

Activate is what actually takes the user to that worksheet.

Putting it all together, we get: Worksheets("Sheet2").Activate

It's as simple as that.

This time, let's navigate to the second worksheet using its index number.

All we do is to replace "Sheet2" from the last example with 2 .

3e8969b16e6f131afc29445b0f9a02ac.jpg

2 is the index number of the desired worksheet and Activate is what takes us to the desired worksheet.

This example can be confusing if you are not used to using index numbers, but index numbers are really helpful when you have to do something like loop through all of the worksheets in the workbook.

Basically, just reference the desired worksheet, however you want, and then type .Activate after it.

You should not use the Activate feature to navigate to a worksheet so that you can get data from it or put data into it. This is very bad Macro/VBA design and it will make your life hell later on. To get data from separate worksheets, read this tutorial: Select Data from Separate Worksheets with Macros VBA in Excel .

This feature should be used when you want the user to end up on a specific worksheet.

Make sure to download the sample file attached to this tutorial so that you can see this macro in Excel and work with it.

Question? Ask it in our Excel Forum

Excel vba course - from beginner to expert.

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Course - Beginner to Expert

Subscribe for weekly tutorials, bonus: subscribe now to download our top tutorials ebook.

The link to our top 15 tutorials has been sent to you, check your email to download it!

(If you don't see the email, check your Spam or Promotions folder and make sure to add us as a contact so you get our emails in the future.)

Excel Forum

200+ Video Lessons 50+ Hours of Video 200+ Excel Guides

activate a worksheet

Activate a worksheet using VBA

Calling the Activate method of a Worksheet object makes that sheet the active sheet, meaning it visually is on top and all macro actions that use ranges without explicitly specifying which sheet will use the ActiveSheet.

Activate and Select

You can Select multiple sheets at the same time ...

... but you can only Activate one

With Activate, if you presently have more than one sheet selected, the ActiveSheet will change, but all selected sheets will remain selected.

With Select, the selected sheet becomes the ActiveSheet. If more sheets are selected (as above), the first in the array is made active sheet.

Save Up to 85% LIMITED TIME OFFER

Project Management Templates

Activate Workbook Or Worksheet in Excel VBA

Activate Workbook or Worksheet

Table Of Contents

Vba code library, vba reference, vba projects, full access with source code.

Ultimate TOC Builder VBA Project

Designed and Developed by PNRao

Full Access with VBA Source Code

Well Commented Codes Lines

Creative and Professional Design

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

Description:

Sometimes we may want to Activate Workbook Or Worksheet using Excel VBA.You can use Activate method in VBA to Select the required Workbook Or WorkSheet using VBA and perform required tasks.

For example, we may have 5 worksheets, we have to do some tasks in Sheet3. In order to perform some tasks we need to activate the worksheet then perform the tasks.

Activate Workbook Or WorkSheet with VBA- Example Cases:

  • You may want to Activate Workbook Or Work Sheet
  • You may want to Activate Workbook Or Work Sheet using object

VBA Activate Workbook or Worksheet

If you know the name of the workbook, you can use the following VBA code to Activate Workbook or worksheet. For example workbook name is “Book2” which we want to activate, it contains sheet named as “Sheet1”. Using following code you can Choose the required Workbook and Sheet, and Activate Workbook Or Work Sheet using Excel VBA.

Instructions:

  • Open an excel workbook
  • Press Alt+F11 to open VBA Editor
  • Insert a new module from Insert menu
  • Copy the above code and Paste in the code window
  • Press F5 to see the output
  • You should see Aactivated workbook and Worksheet
  • Save the file as macro enabled workbook

Activate Workbook or Worksheet using object

You can create object for workbook and worksheet in the following way.You can use object to activate Workbook or Worksheet. In this approach, you will assign the Workbook to any object and select Workbook with VBA to perform required operations using Excel Macros.

Explanation:

  • We declared two objects for workbook and worksheet
  • We have added a new workbook and assigned to workbook object
  • We set the Worksheet to worksheet object
  • We activated the worksheet by refering the worksheet object
  • You should see output as shown above

VBA to Select Workbook or Worksheet in Excel

When we have multiple worksheets in a workbook, we can select required sheet using VBA. We can also select required workbook when we have more than one Excel Workbook is opened. It is good practice to set the required workbook or sheet to an object. And refer it whenever it is required. This helps you to accessing workbook or sheet without selecting or activating it.

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.

All-in-One Pack 120+ Project Management Templates

Essential pack 50+ pm 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, related posts.

VBA ActiveSheet – Excel Active Sheet Object

VBA ActiveSheet – Excel Active Sheet Object

Excel VBA ColorIndex

Excel VBA ColorIndex

Excel VBA Copy Range to Another Sheet with Formatting

Excel VBA Copy Range to Another Sheet with Formatting

Hi, If you worksheet has a space in its name eg: ‘Sheet 1’, how would you reference that? (Workbooks(“Book2.xls”).Sheets(“Sheet1”).Activate)

Thanks-PNRao!

I have just got a very simple question.

My current macro pastes into a specific file Windows(“test file.xls”).Activate with this code. I would like to use this macro on various files with different names. Therefore I want it to paste onto my active worksheet, and am unsure of what code it should be .

Please help!

Leave A Comment Cancel reply

You must be logged in to post a comment.

Effectively Manage Your Projects and  Resources

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Download Free Excel 2007, 2010, 2013 Add-in for Creating Innovative Dashboards, Tools for Data Mining, Analysis, Visualization. Learn VBA for MS Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Analysistabs Logo

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

Analyst Cave

Simply the best place to learn Excel and Analytics

Home » Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc.

excel vba worksheets tutorial

Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc.

Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets , how to Select Worksheets , how to Activate Worksheets , Selecting vs Activating Worksheets … and everything else you need to know about the VBA Worksheet in general.

ThisWorkbook vs ActiveWorkbook

Some Excel Worksheets

  • ThisWorkbook – refers to the Workbook in which the VBA macro is running
  • ActiveWorkbook – refers to the Workbook which is in the topmost Excel Window

It is extra important to understand this difference and I encourage you to read my post on this topic first.

The Excel VBA Object Hierarchy

Excel vba object hierarchy

At the top, at the root we have our Excel Application . The Excel Application represents the entire Excel process. Further down the tree we have our Workbooks . Each Application contains a collection of Workbooks. Looking into a single Workbook we will notice it contains a collection of Worksheets . Worksheets on the other hand as you know can define Ranges (not the same a single cells). Using a Range we can access its cells Values or Formulas .

Accessing VBA Worksheets

Now that we have that behind us lets explore the different ways in which we can access Worksheets in VBA:

ActiveWorkbook VBA Worksheets

The sheets and worksheets collections.

Sheets within the ActiveWorkbook :

Similarly we can use Worksheets instead of Sheets.

ThisWorkbook VBA Worksheets

Sheets within the ThisWorkbook :

Worksheet VBA Name vs Excel Name

Worksheet VBA Name vs Excel Name

Using the VBA Name of a Worksheet

Worksheet VBA Name

Using the Excel Name of a Worksheet

Referring to an Excel Worksheet from VBA is not a recommended practice . Below I am referring to the Worksheet I named Excel Name in my example above.

Not what you notice is that compared to acquiring the Worksheet by its VBA Name, when using the defaults Worksheets or Sheets object you land with the said Worsheet, but from the ActiveWorkbook. Usually this doesn’t do much of a difference. But I am highlighting that this is simply another place to make a common mistake. See both versions below :

The VBA ActiveSheet

As with Active vs ThisWorkbook you need to first understand the difference between Selecting a Worksheet and Activating it .

Selected vs Activated Worksheet , the differences:

  • Selected Worksheet – one or more Worksheets that have been selected within an Excel Window. Each Workbook has its own set of Selected Worksheets
  • ActiveWorksheet – the current Worksheet you are viewing and working in. The only top-most Worksheet in all Application Workbooks

From the definition above you see that there can be more than 1 Selected Worksheet, while only 1 ActiveSheet. In fact both are totally different things. Lets explore this in some examples:

Example 1: Explaining ActiveSheet

vba worksheets example

Lets open VBE in Book1.xlsm . We will add the following piece of code:

vba activesheet

Example 2: Explaining Selected vs ActiveSheet

Let us consider the same example. Open Book1.xlsm and add the following code to the VBE:

Now do the following:

  • Activate workbook Book2.xlsm
  • Select a number of Worksheets (at least 2)
  • Run Book1.xlsm!SelectedVSActive macro

vba activesheet vs selected

  • Only the top-most Worksheet is considered as the ActiveSheet
  • The ActiveSheet is ALWAYS considered as a Selected Worksheet within the ActiveWindow (this last part is important)

What happens if you change ActiveWindow to e.g. Windows(2) ? You will see a different set of Selected Worksheets. Why? Because all Workbooks have Selected Worksheets. Hope you see the difference.

Activating VBA Worksheets

Activating Worksheet is easy in VBA. You do it using the Activate property of Worksheets. How to activate any Worksheet using VBA?

Activate Worksheet examples

A few examples below:

Selecting VBA Worksheets

Selecting Worksheets is a little more difficult as we may want to select a single Worksheet or more.

Selecting a single VBA Worksheet

Selecting a single VBA Worksheet is simple. Just use the Select property of a Worksheet.

Selecting a single Worksheet examples

Selecting multiple vba worksheets.

Selecting more than 1 Worksheet is a little more complex as we need to leverage the VBA Array function :

This is not the most elegant way of doing it. Fortunately the Select function has an argument called Replace . Setting it to false will mean that the previously selected is not to be deselected. Thus we can extend our selections like this:

This will select the first three Worksheets in our ActiveWorkbook.

Deleting VBA Worksheets

To delete a VBA Worksheet we need to turn off the Application.DisplayAlerts

You can delete multiple Worksheets in a similar fashion as how you can do a multiple Worksheet Select:

Copying VBA Worksheets

You can copy Worksheets within a Workbook or from other open Workbooks.

Moving VBA Worksheets

You can move Worksheets within a Workbook or from other open Workbooks.

Other VBA Worksheets properties

Below are other VBA Worksheet properties worth mentioning:

Worksheet propertyDescriptionExampleCalculateCalculates all dirty formulas on the current Worksheet

NameGet or set the Excel Name of the Worksheet

Display, Hide or make Worksheet VeryHidden. Possible values:

  • xlSheetVisible
  • xlSheetVeryHidden
  • xlSheetHidden

To learn more read Unhide sheets in Excel / Unhide all Sheets in Excel using VBA .

Merging worksheets / tables in Excel

Download VBA Time Saver

VBA Time Saver Kit

Download LOOKUP Wizard

Excel Lookup Wizard Book1

SEE LATEST POSTS

Spreadsheet Planet

How to Activate Workbook Using VBA

When working with multiple workbooks in Excel VBA, activating or switching focus to the right workbook is crucial. Activating a workbook brings it to the front of the screen and makes it active.

This activation helps ensure that your code operates on the intended workbook. 

Activating workbooks can help you specify the source and destination for operations like copying and pasting data between workbooks. For instance, you can activate the workbook containing the data you want to copy, then activate the destination workbook and paste it into it.  

We use the ‘Activate’ method of the ‘Workbooks’ collection to activate a workbook. The ‘Workbooks’ collection is a built-in object that represents all open Excel workbooks in a specific instance of Excel.

In this tutorial, I show you five examples of how to activate a workbook using VBA. 

Table of Contents

Example #1: Activate a Workbook Using its Full Name

You can activate a workbook by specifying its full name if it’s open.

The following example code activates the workbook “2023 Sales Report.xlsm” using its full name:

This code activates the “2023 Sales Report.xlsm” workbook, activates “Sheet1” within that workbook, and sets the value of cell A1 to 200.

It assumes that the workbook with the specified name exists and is open in the Excel application.

You can use the following alternative code that utilizes the “Name” property of the workbook object:

The code searches all open workbooks to find “2023 Sales Report. xlsm”.

If the workbook is found, it is activated, and a message stating, “Workbook found and activated” is displayed. If the workbook is not found, a different message reads, “Workbook not found.”

Example #2: Activate a Workbook Using its Partial Name

To activate a workbook using its partial name (a substring of its full name), you can use a VBA macro that searches through all open workbooks and activates the one that contains the partial name. 

Here’s a sample VBA code to do this:

searches for a workbook whose name contains a specific partial name (“Tutorials” in this case) among all open workbooks.

If it finds such a workbook, it activates it and shows a message box stating “Workbook found and activated.”

If it doesn’t find any workbook with the partial name, a message box saying “Workbook with partial name ‘Tutorials’ not found” is displayed. The subroutine uses a flag variable foundWorkbook to keep track of whether a matching workbook was found.

Note: The ‘InStr’ function performs a case-insensitive search for the “partialName.”

Example #3: Activate a Workbook Using an Object Variable

You can activate a workbook by assigning it to an object variable and using the variable to activate it. See the code example below:

The code checks whether the “Sales Report.xlsm” workbook is open.

If it is, it will activate the workbook and then activate “Sheet1” to set the value of cell A1 as 55.

However, if it is not open, it will display a message saying “Workbook is not open” and exit the subroutine.

Example #4: Activate a Workbook Using its Index Number

You can activate a workbook in the ‘Workbooks’ collection using its index position. The index numbers represent the order in which workbooks were opened or created during the current Excel session. 

The index starts at 1 for the first open workbook and increments with each additional workbook.

The below code activates the workbook with index number 2 in the ‘Workbooks’ collection:

The code example checks if the workbook with index number 2 exists, and if it is, it activates the workbook and then activates “Sheet1” to set the value of cell A1 to 75.

If the workbook is not found, it displays the message “Workbook with index 2 does not exist” and exits the subroutine.

Example #5: Activate a Workbook Using the “Like” Operator

You can use the “Like” operator in VBA to activate a workbook whose name matches a specific pattern. The “Like” operator allows you to use wildcard characters such as asterisk (*) to perform pattern matching. 

Here’s an example of a VBA code that activates a workbook with a name that matches a specific pattern:

In the code above:

  • ‘wbNamePattern’ is the pattern used to match workbooks. For this example, it’s set to “2023*.xlsm,” which matches workbooks starting with “2023” and ending with “.xlsm.”
  • The code then loops through all open workbooks using a “For Each” loop.
  • For each workbook, it checks if the name matches the specified pattern using the “Like” operator.
  • If a matching workbook is found, it activates it and then activates “Sheet1” to set the value of cell A1 to 105 and exits the loop.
  • If no matching workbook is found, a message box displays “No matching workbook found.”

In this tutorial, we walked through five different code examples showing how you can use VBA to activate workbooks in Excel.

Activating workbooks is a common task when automating things in Excel with VBA. I tried to provide a nice mix of examples so you can see different techniques for activating workbooks depending on your needs.

Let me know if any part was confusing or if you have any other questions!

Happy coding!

Other Excel articles you may also like:

  • What is VBA in Excel?
  • Create a New Workbook Using VBA in Excel
  • How to Delete a Sheet in Excel Using VBA
  • How to Close Workbook Using VBA
  • How to Save Workbook Using VBA
  • How to Open Excel Workbook Using VBA
  • Switch Between Excel Workbooks
  • Delete Files in a Folder Using VBA in Excel

' src=

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Activate Workbook: Step-by-Step Examples to Activate Workbooks with Macros

Step-by-Step Examples to Activate Workbooks with Macros

This Excel VBA Activate Workbook Tutorial is currently under development. Subscribe to the Power Spreadsheets Newsletter to get future updates to this Excel VBA Tutorial .

Use the following Table of Contents to navigate to the Section you're interested in.

Table of Contents

Excel VBA Activate This Workbook

Vba code to activate this workbook.

To activate this workbook , use the following statement in the applicable procedure.

Process to Activate This Workbook with VBA

  • Refer to this workbook (the workbook where the macro is stored).
  • Activate the workbook.

Main VBA Constructs Used to Activate This Workbook

(1) Application.ThisWorkbook property.

Returns a Workbook object representing the workbook where the macro is stored (the workbook where the macro is running).

(2) Workbook object.

Represents an Excel workbook.

(3) Workbook.Activate method.

Activates the (first) window associated with a workbook.

Macro Example to Activate This Workbook

The macro below does the following:

  • Activate this workbook (the workbook where the macro is stored).
  • Maximize the active window.

Effects of Executing Macro Example to Activate This Workbook

The image below illustrates the effects of using the macro example. In this example:

  • 2 workbooks (“Excel VBA Activate Workbook” and “Book1”) are open.
  • The “Book1” workbook is the active workbook.
  • The macro example is stored in the “Excel VBA Activate Workbook” workbook.

When the macro is executed, Excel:

  • Activates this workbook (“Excel VBA Activate Workbook”; the workbook where the macro example is stored); and
  • Maximizes the active window.

Excel VBA Activate Workbook by Filename

Vba code to activate workbook by filename.

To activate a workbook by filename , use the following structure/template in the applicable statement.

Process to Activate Workbook by Filename with VBA

  • Refer to the workbook to activate by filename.
  • Activate the applicable workbook.

Main VBA Constructs Used to Activate Workbook by Filename

(1) Application.Workbooks property.

Returns a Workbooks collection representing all open workbooks.

(2) Workbooks object.

Represents all open workbooks.

(3) Workbooks.Item property.

Returns a specific Workbook object from the applicable Workbooks collection.

(4) Workbook object.

(5) Workbook.Activate method.

Cues to Activate Workbook by Filename with VBA

  • Consider whether you must wrap the workbook filename in double quotes and parentheses ((“Filename”)).
  • As a general rule: Include the complete filename, including file extension (for ex., “Filename.xlsm”), of a previously saved workbook.

Macro Example to Activate Workbook by Filename

  • Activate the workbook with filename “Excel VBA Activate Workbook.xlsm”.

Effects of Executing Macro Example to Activate Workbook by Filename

  • 2 workbooks (“Excel VBA Activate Workbook.xlsm” and “Book1”) are open.
  • Activates the workbook with filename “Excel VBA Activate Workbook.xlsm”; and

Excel VBA Activate Workbook with Partial Filename (with Left, Mid, or Right Functions)

Vba code to activate workbook with partial filename (with left, mid, or right functions).

To activate a workbook with partial filename (with the Left, Mid, or Right functions) , use the following structure/template in the applicable procedure.

Process to Activate Workbook with Partial Filename (with Left, Mid, or Right Functions)

  • Declare iteration object variable (usually of the Workbook object data type).
  • Loop through all open workbooks.
  • Test whether the applicable part (for example, the beginning or end) of the name of the workbook (the loop is currently iterating through) matches the (known) partial filename (of the workbook to activate).
  • If the condition is met (the applicable part of the name of the applicable workbook matches the partial filename of the workbook to activate), activate the workbook (the loop is currently iterating through).

Main VBA Constructs Used to Activate Workbook with Partial Filename (with Left, Mid, or Right Functions)

(1) Variable declaration statement.

One of the following 4 statements:

  • Dim: Declares variables and allocates storage space.
  • Used at the module level.
  • Declares module-level variables and allocates storage space.
  • Declares global variables and allocates storage space.
  • Static: Declares static variables and allocates storage space.

(3) For Each… Next statement.

Repeats a set of statements for each element in an array or collection.

(4) Application.Workbooks property.

(5) Workbooks object.

(6) Object variable.

A named storage location containing data (a reference to an object) that can be modified during procedure execution.

(7) If… Then… Else statement.

Conditionally executes a set of statements, depending on the value returned by a logical expression.

(8) VBA built-in text function.

One (or more) of the following VBA built-in text functions:

  • Left: Returns a string containing a specific number of characters from the start (left side) of a string.
  • Mid: Returns a string containing a specific number of characters from the middle (starting at a specific position) of a string.
  • Right: Returns a string containing a specific number of characters from the end (right side) of a string.
  • InStr: Returns the position of the first occurrence of one string inside another string.

(9) String.

A sequence of contiguous characters.

(10) Workbook.Activate method.

Cues to Activate Workbook with Partial Filename (with Left, Mid, or Right Functions) with VBA

  • The iteration object variable.
  • The data type (usually Workbook object) of the iteration object variable.
  • Using the Dim statement; and
  • As of the Workbook object data type.
  • If the scope of the iteration object variable is module-level or global, follow the applicable rules for (module-level or global) variable declaration.
  • If… Then. This is the version I use in the structure/template above.
  • If… Then… Else.
  • If… Then… ElseIf.
  • If… Then… ElseIf… Else.
  • The appropriate VBA built-in text function you work with depends on the case you deal with (for example, the structure and position of the known partial filename).
  • The (main) string you use as one of the arguments of the VBA built-in text function (the string from which you extract characters) is the name of the applicable workbook (the For Each… Next statement is currently iterating through).
  • Work with the Workbook.Name property to obtain the name of the applicable workbook. The Workbook.Name property returns a workbook's name.
  • When specifying the (known) partial filename, consider whether you must wrap this partial filename in double quotes (“PartialWorkbookFilename”).

Macro Example to Activate Workbook with Partial Filename (with Left, Mid, or Right Functions)

  • Declare an object variable (iWorkbook) of the Workbook object data type.
  • Test whether the first/leftmost 9 letters of the name of the workbook (the loop is currently iterating through) spell “Excel VBA”.
  • If the condition is met (the first/leftmost 9 letters of the name of the applicable workbook spell “Excel VBA”), activate the workbook (the loop is currently iterating through).

Effects of Executing Macro Example to Activate Workbook with Partial Filename (with Left, Mid, or Right Functions)

  • 10 workbooks (“Excel VBA Activate Workbook.xlsm”, and “Book1” through “Book9”) are open.
  • The “Book1” workbook is the active workbook.

When the macro is executed, Excel activates the workbook whose filename (Excel VBA Activate Workbook.xlsm) contains the (known) partial filename (Excel VBA).

Excel VBA Activate Workbook with Partial Filename (with InStr Function)

Vba code to activate workbook with partial filename (with instr function).

To activate a workbook with partial filename (with the InStr function) , use the following structure/template in the applicable procedure.

Process to Activate Workbook with Partial Filename (with InStr Function)

  • Test whether the name of the workbook (the loop is currently iterating through) contains the (known) partial filename (of the workbook to activate).
  • If the condition is met (the name of the applicable workbook contains the partial filename of the workbook to activate), activate the workbook (the loop is currently iterating through).

Main VBA Constructs Used to Activate Workbook with Partial Filename (with InStr Function)

(8) InStr function.

Returns the position of the first occurrence of one string inside another string.

Accepts the following 4 arguments:

  • Optional argument.
  • The starting position for the search.
  • If omitted, the search begins at the first character.
  • Required argument.
  • The string you search in.
  • The string you search for.
  • The string comparison method.
  • If omitted, the module's Option Compare setting applies.

(9) Workbook.Name property.

Returns a workbook's name.

(10) String.

(11) Greater than operator (>).

Compares 2 expressions and returns True, False, or Null as follows:

  • True: If Expression1 > Expression2.
  • False: If Expression1 <= Expression2.
  • Null: If (either) Expression1 or Expression2 are Null.

(12) Workbook.Activate method.

Cues to Activate Workbook with Partial Filename (with InStr Function) with VBA

  • string1: The name of the workbook the loop is currently iterating through, as returned by the Workbook.Name property.
  • string2: The (known) partial filename.
  • compare: vbBinaryCompare. vbBinaryCompare (usually) results in a case sensitive comparison.
  • When specifying the (known) partial filename (as string2 argument of the InStr function), consider whether you must wrap this partial filename in double quotes (“PartialWorkbookFilename”).

Macro Example to Activate Workbook with Partial Filename (with InStr Function)

  • Test whether the name of the workbook (the loop is currently iterating through) contains “Excel VBA”.
  • If the condition is met (the name of the applicable workbook contains “Excel VBA”), activate the workbook (the loop is currently iterating through).

Effects of Executing Macro Example to Activate Workbook with Partial Filename (with InStr Function)

Excel vba activate workbook using wildcard, vba code to activate workbook using wildcard.

To activate a workbook using a wildcard , use the following structure/template in the applicable procedure.

Process to Activate Workbook Using Wildcard

  • Test whether the name of the workbook (the loop is currently iterating through) is like the (known) filename (of the workbook to activate). Use wildcards (as necessary) to specify the filename of the workbook to activate.
  • If the condition is met (the name of the applicable workbook is like the filename -including any wildcards- of the workbook to activate), activate the workbook (the loop is currently iterating through).

Main VBA Constructs Used to Activate Workbook Using Wildcard

(8) Workbook.Name property.

(9) Like operator.

Compares a string against a pattern and returns True, False, or Null as follows:

  • True: If the string matches the pattern.
  • False: If the string doesn't match the pattern.
  • Null: If (either) the string or the pattern are Null.

You can combine the following elements when specifying the pattern:

  • Wildcard characters.
  • Character lists.
  • Character ranges.

(11) Workbook.Activate method.

Cues to Activate Workbook Using Wildcard with VBA

  • Use the Like operator to compare the string returned by the Workbook.Name property (the name of the workbook the loop is currently iterating through) against a string with the filename (including any wildcards) of the workbook to activate.
  • ?: Represents any single character.
  • *: Represents any sequence of 0 or more characters.
  • When specifying the pattern the Like operator works with (the filename -including any wildcards- of the workbook to activate), consider whether you must wrap this partial filename in double quotes (“WorkbookNameUsingWildcard”).

Macro Example to Activate Workbook Using Wildcard

  • Starts with “Excel VBA”;
  • Followed by any sequence of characters; and
  • Ends with the “.xlsm” file extension.
  • If the condition is met (the name of the applicable workbook is like the specified filename, including the * wildcard), activate the workbook (the loop is currently iterating through).

Effects of Executing Macro Example to Activate Workbook Using Wildcard

When the macro is executed, Excel activates the workbook whose filename (Excel VBA Activate Workbook.xlsm) has the following structure:

Excel VBA Activate Workbook and Worksheet

Vba code to activate workbook and worksheet.

To activate a workbook and a worksheet , use the following structure/template in the applicable statement.

Process to Activate Workbook and Worksheet with VBA

  • Refer to the workbook and worksheet to activate.
  • Activate the applicable worksheet.

Main VBA Constructs Used to Activate Workbook and Worksheet

(1) Workbook object.

(2) Workbook.Worksheets property.

Returns a Sheets collection representing all worksheets in the applicable workbook.

(3) Worksheets object.

Represents all worksheets in the applicable workbook.

(4) Worksheets.Item property.

Returns a specific Worksheet object from the applicable Worksheets collection.

(5) Worksheet object.

Represents a worksheet.

(6) Worksheet.Activate method.

Activates the applicable worksheet.

Cues to Activate Workbook and Worksheet with VBA

  • Application.Workbooks property, Workbooks object, and Workbooks.Item property.
  • Application.ThisWorkbook property.
  • Represents the position of the Worksheet object in the Worksheets collection.
  • Wrapped in parentheses ((IndexNumber)).
  • Included after the reference to the Worksheets collection (Worksheets(IndexNumber)).
  • Double quotes (“WorksheetName”); and
  • Parentheses ((“WorksheetName”)).
  • Included after the reference to the Worksheets collection (Worksheets(“WorksheetName”)).
  • You can (also) use a Worksheet object's CodeName property (Worksheet.CodeName property) to refer to the applicable worksheet.

Macro Example to Activate Workbook and Worksheet

  • The “Excel VBA Activate Workbook.xlsm” workbook; and
  • The “Activate Workbook and Worksheet” worksheet.

Effects of Executing Macro Example to Activate Workbook and Worksheet

  • 2 workbooks (“Excel VBA Activate Workbook.xlsm” and “Book1”) are open.
  • The “Sheet1” worksheet is the active worksheet in both (“Excel VBA Activate Workbook.xlsm” and “Book1”) workbooks.
  • The “Excel VBA Activate Workbook.xlsm” workbook; and

Excel VBA Activate Workbook and Chart Sheet

Vba code to activate workbook and chart sheet.

To activate a workbook and a chart sheet , use the following structure/template in the applicable statement.

Process to Activate Workbook and Chart Sheet with VBA

  • Refer to the workbook and chart sheet to activate.
  • Activate the applicable chart sheet.

Main VBA Constructs Used to Activate Workbook and Chart Sheet

(2) Workbook.Charts property.

Returns a Sheets collection representing all chart sheets in the applicable workbook.

(3) Charts object.

Represents all chart sheets in the applicable workbook.

(4) Charts.Item property.

Returns a specific Chart object from the applicable Charts collection.

(5) Chart object.

Represents a chart in a workbook. The chart can be either of the following:

  • A chart sheet.
  • An embedded chart (not the subject of this Section).

(6) Chart.Activate method.

Activates the applicable chart.

Cues to Activate Workbook and Chart Sheet with VBA

  • Represents the position of the Chart object in the Charts collection.
  • Included after the reference to the Charts collection (Charts(IndexNumber)).
  • Double quotes (“ChartSheetName”); and
  • Parentheses ((“ChartSheetName”)).
  • Included after the reference to the Charts collection (Charts(“ChartSheetName”)).
  • You can (also) use a Chart object's CodeName property (Chart.CodeName property) to refer to the applicable chart sheet.

Macro Example to Activate Workbook and Chart Sheet

  • The “Activate Workbook Chart Sheet” chart sheet.

Effects of Executing Macro Example to Activate Workbook and Chart Sheet

  • The “Sheet1” worksheet is the active sheet in both (“Excel VBA Activate Workbook.xlsm” and “Book1”) workbooks.

Excel VBA Activate Workbook with Variable Name

Vba code to activate workbook with variable name.

To activate a workbook with variable name (where the workbook filename is held by a variable) , use the following structure/template in the applicable procedure.

Process to Activate Workbook with Variable Name

  • Declare variable (usually of the String data type) to represent workbook filename.
  • Assign workbook filename to variable.
  • Refer to the workbook to activate by using the applicable variable name (representing the workbook filename).

Main VBA Constructs Used to Activate Workbook with Variable Name

(2) String data type.

Holds textual data (a sequence of characters representing the characters themselves).

(3) Assignment operator (=).

Assigns a value to a variable or property.

(6) Workbooks.Item property.

(7) Workbook object.

(8) Variable.

A named storage location containing data that can be modified during procedure execution.

(9) Workbook.Activate method.

Cues to Activate Workbook with Variable Name with VBA

  • The variable representing the workbook filename.
  • The data type (usually String) of the variable representing the workbook filename.
  • As of the String data type.
  • If the scope of the variable representing the workbook filename is module-level or global, follow the applicable rules for (module-level or global) variable declaration.
  • Include the complete filename, including file extension (for ex., “Filename.xlsm”), of a previously saved workbook.
  • Do not include the file path.

Macro Example to Activate Workbook with Variable Name

  • Declare a variable (WorkbookFilename) of the String data type.
  • Assign a string (Excel VBA Activate Workbook.xlsm) to the WorkbookFilename variable.
  • Activate the workbook whose filename is represented by the WorkbookFilename variable.

Effects of Executing Macro Example to Activate Workbook with Variable Name

  • Activates the workbook whose filename (Excel VBA Activate Workbook.xlsm) is held by a variable (WorkbookFilename); and

Excel VBA Activate Workbook with Object Variable Name

Vba code to activate workbook with object variable name.

To activate a workbook with object variable name (where the Workbook object is represented by an object variable) , use the following structure/template in the applicable procedure.

Process to Activate Workbook with Object Variable Name

  • Declare object variable (usually of the Workbook object data type) to represent workbook.
  • Assign Workbook object reference to object variable.
  • Refer to the workbook to activate by using the applicable object variable name (representing the workbook).

Main VBA Constructs Used to Activate Workbook with Object Variable Name

(3) Set statement.

Assigns an object reference to a variable or property.

(7) Object Variable.

(8) Workbook.Activate method.

Cues to Activate Workbook with Object Variable Name with VBA

  • The object variable representing the workbook.
  • The data type (usually Workbook object) of the object variable representing the workbook.
  • If the scope of the object variable representing the workbook is module-level or global, follow the applicable rules for (module-level or global) variable declaration.

Macro Example to Activate Workbook with Object Variable Name

  • Declare an object variable (WorkbookObjectVariable) of the Workbook object data type.
  • Assign a Workbook object reference (Excel VBA Activate Workbook.xlsm) to the WorkbookObjectVariable object variable.
  • Activate the workbook represented by the WorkbookObjectVariable object variable.

Effects of Executing Macro Example to Activate Workbook with Object Variable Name

  • Activates the workbook whose reference (Excel VBA Activate Workbook.xlsm) is held by an object variable (WorkbookObjectVariable); and

Excel VBA Open and Activate Workbook

Vba code to open and activate workbook.

To open and activate a workbook , use the following structure/template in the applicable statement.

Process to Open and Activate Workbook with VBA

  • Open the workbook with the Workbooks.Open method.

Main VBA Constructs Used to Open and Activate Workbook

(1) Workbooks.Open method.

Opens a workbook.

(2) Filename parameter.

  • First parameter of the Workbooks.Open method.
  • Filename of the workbook to open.

Cues to Open and Activate Workbook with VBA

  • When you open a workbook with the Workbooks.Open method, the opened workbook becomes the active workbook.
  • Consider whether you must wrap the workbook path and filename in double quotes (“PathAndFilename”).
  • Include the full path and filename (including file extension).
  • If the workbook is stored in the default file folder (where Excel currently saves files by default), you can exclude the workbook's path (use only the filename, including file extension).

Macro Example to Open and Activate Workbook

The macro below opens (and activates) the workbook with filename “Excel VBA Open and Activate Workbook.xlsx” stored in the Desktop folder.

Effects of Executing Macro Example to Open and Activate Workbook

The image below illustrates the effects of using the macro example.

In this example (only) 1 workbook (“Excel VBA Activate Workbook”) is open. This is (also) the active workbook.

When the macro is executed, Excel opens and activates the workbook with filename “Excel VBA Open and Activate Workbook.xlsx” stored in the Desktop folder.

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

  • Free Excel VBA Email Course
  • Excel Macro Tutorial for Beginners
  • Excel Power Query (Get and Transform) Tutorial for Beginners
  • Excel Keyboard Shortcut Cheat Sheet
  • Excel Resources

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Workbook.ActiveSheet property (Excel)

  • 6 contributors

Returns a Worksheet object that represents the active sheet (the sheet on top) in the active workbook or specified workbook. Returns Nothing if no sheet is active.

expression . ActiveSheet

expression A variable that represents a Workbook object.

Using the ActiveSheet property without an object qualifier returns the active sheet in the active workbook in the active window.

If a workbook appears in more than one window, the active sheet might be different in different windows.

This example displays the name of the active sheet.

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.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

VBA Activate Workbook (Excel File)

- Written by Puneet

When you are working with multiple workbooks at the same time, I mean, when you have more than one workbook open at the same time then you need to know the method that can help you activate a workbook that you want to work on.

To activate a workbook using VBA, you need to use the Workbook.Activate method. In this method, you need to specify the workbook name using the Workbook object. It also allows you to use the workbook number instead of the workbook name, but you can only refer to the open workbooks.

In this tutorial, we look at different ways to use this method.

Steps to Activate a Workbook

  • Type “Workbooks” to use the workbook object.
  • Specify the workbook name in the double quotation marks.
  • Enter a dot (.) to get the list of properties and methods.
  • Select the Activate method from the list or you can also type it.
  • In the end, run the code to activate the workbook.

Note: If you try to activate a workbook that is not open, VBA will show an error.

Related: Activate a Worksheet using VBA

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Activate a Workbook by using the Number

When you have multiple workbooks open all those workbooks are part of the workbook collection and have a number that you can use to refer to and then you can use the activate method with it. Here’s the code:

And if you are trying to activate a workbook using a number that doesn’t exist, VBA will show you an error Run-time error ‘9’ (Subscript out of Range).

Activate ThisWorkbook

You can refer to the workbook where you are writing code by using the ThisWorkbook property. Let’s say you have five workbooks open at the same time but you are working on the “Book1.xlsm”, so when you run the following code, it will activate the “Book1.xlsm”.

Check Before Activating a Workbook

As I said, when you try to activate a workbook that is not opened VBA will show you an error. To deal with this problem the best way is to check for the workbook name first (if it’s open or not) and then activate it.

By using the above code, you can specify a workbook name and this will first check for that workbook in all the open workbooks, and if it finds the workbook, it will activate it.

  • When you are using the name of the workbook make sure to use the correct file extension
  • If you want to activate a workbook that is not yet saved, then you need to use only the name of that workbook without suffixing the file extension.

Related Tutorials

  • Copy an Excel File (Workbook) using VBA
  • VBA Close Workbook (Excel File)
  • VBA Combine Workbooks (Excel Files)
  • VBA Create New Workbook (Excel File)
  • VBA Delete Workbook (Excel File)
  • VBA Open Workbook (Excel File)
  • VBA Protect/Unprotect Workbook (Excel File)
  • VBA Rename Workbook (Excel File)
  • VBA Save Workbook (Excel File)
  • VBA ThisWorkbook (Current Excel File)
  • VBA Workbook – A Guide to Work with Workbooks in VBA
  • VBA Check IF a Workbook Exists in a Folder (Excel File)
  • VBA Check IF a Workbook is Open (Excel File)
  • Save an Excel Macro-Enabled Workbook (.xlsm File Type)

how to make worksheet active vba

IMAGES

  1. Excel/VBA Active sheet: Learn to Access, Rename, Activate with 4 Examples

    how to make worksheet active vba

  2. Activate A Worksheet Vba

    how to make worksheet active vba

  3. Excel Vba Activate Worksheets

    how to make worksheet active vba

  4. VBA WorkSheets

    how to make worksheet active vba

  5. VBA Activate Workbook or Worksheet

    how to make worksheet active vba

  6. VBA Active Cell

    how to make worksheet active vba

VIDEO

  1. Excel VBA Userform Add Save Data Into Multiple Sheets

  2. Test yourself on VBA 00004| Workbook Name #advanced_excel #excel #excelacademy #exceleducation #vba

  3. Select Active Row

  4. How To Define A Worksheet Reference (Excel VBA)

  5. Highlight Active Row & Column in Excel

  6. Excek VBA

COMMENTS

  1. VBA ActiveSheet & How To Activate or Select a Sheet

    To set the ActiveSheet use Worksheet.Activate: Worksheets("Input").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:

  2. Activate Sheet Using VBA in Excel (Worksheet.Activate)

    VBA Rename Sheet in Excel; Using Active Cell in VBA in Excel (Examples) VBA Create New Sheet (Sheets.Add) Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best ...

  3. Worksheet.Activate method (Excel)

    Makes the current sheet the active sheet. Syntax. expression.Activate. expression A variable that represents a Worksheet object. Remarks. Calling this method is equivalent to choosing the sheet's tab. Example. This example activates Sheet1. Worksheets("Sheet1").Activate Support and feedback. Have questions or feedback about Office VBA or this ...

  4. VBA Activate Sheet

    Follow the steps listed as follows to declare the variable, assign the worksheet object, and activate the worksheet by using the variable name. Step 1: In the sub procedure macro, declare a variable Ws by using the Dim keyword. Step 2: Once the variable name is given, we need to assign the VBA data type to it.

  5. How to Activate a Sheet using VBA

    Here are some of them. 1. Activate a Worksheet on Opening. If you want to activate a specific worksheet every time when you open the workbook then you name that VBA code auto_open. Sub auto_open() Worksheets("Sheet1").Activate. End Su. 2. Activate a Worksheet and Hide all other.

  6. vba

    Sub activateSheet(sheetname As String) 'activates sheet of specific name. Worksheets(sheetname).Activate. End Sub. Basically you want to make use of the .Activate function. Or you can use the .Select function like so: Sub activateSheet(sheetname As String) 'selects sheet of specific name. Sheets(sheetname).Select.

  7. Excel/VBA Active sheet: Learn to Access, Rename ...

    Display active sheet name in alert example. Step 1: Place a command button in a sheet: Step 2: Double-click on the button in " Design Mode " to open the VBA editor. Step 3: Write this line of code in the click event of the button: Save the code and get back to the sheet containing that button. Step 4:

  8. VBA Activate Sheet

    Step 1: In Module, start writing Subcategory of VBA Activate Sheet in sequence as shown below. Code: Sub VBA_ActivateSheet2() End Sub. Step 2: As we used Sheet command for activating Sheet, here we will use Worksheet command. Code: Sub VBA_ActivateSheet2() Worksheets("First") End Sub.

  9. How to Activate Worksheet in Excel in VBA

    We can use either the name or the number of the Worksheet. The best way is to use the sheet name. Sub activateNewSheet() Worksheets("Sheet2").Activate. 'Worksheets(2).Activate. End Sub. This will make the Sheet2 or the second sheet in the Excel file an active worksheet.

  10. Activate or Navigate to a Worksheet using Macros VBA in Excel

    Let's navigate to, activate, the worksheet called "Sheet2". Select All. Worksheets("Sheet2").Activate. Worksheets ("Shee2") is how we reference the sheet to which we want to navigate. Sheet2 is the name of the sheet to which we want to navigate. Make sure to surround it with double quotation marks. Activate is what actually takes the user to ...

  11. Selecting and Activating Cells

    Selecting Cells on the Active Worksheet. If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells. For example, the following ...

  12. Activate worksheets with code

    Activate a worksheet using VBA. Calling the Activate method of a Worksheet object makes that sheet the active sheet, meaning it visually is on top and all macro actions that use ranges without explicitly specifying which sheet will use the ActiveSheet. Dim wb As Workbook: Set wb = Dim ws As Worksheet: Set ws = wb.Sheets("Sheet2")

  13. Excel VBA Sheets and Worksheets: Step-by-Step Guide and 16 Examples

    To loop through all worksheets in a workbook in reverse order with VBA, follow these steps: Identify the workbook containing the worksheets (Workbook). Identify the Sheets collection representing all worksheets in Workbook (Worksheets). Count the number of worksheets in the Sheets collection (Count).

  14. Activate Workbook Or Worksheet in Excel VBA

    VBA to Select Workbook or Worksheet in Excel. When we have multiple worksheets in a workbook, we can select required sheet using VBA. We can also select required workbook when we have more than one Excel Workbook is opened. It is good practice to set the required workbook or sheet to an object. And refer it whenever it is required.

  15. Excel Worksheets Tutorial: VBA Activesheet vs Worksheets

    Some Excel WorksheetsLets start with the basics. Before we start I want to stress and remind the difference between ActiveWorkbooks and ThisWorksbooks. In short: ThisWorkbook - refers to the Workbook in which the VBA macro is running. ActiveWorkbook - refers to the Workbook which is in the topmost Excel Window.

  16. How to Activate Workbook Using VBA

    To activate a workbook using its partial name (a substring of its full name), you can use a VBA macro that searches through all open workbooks and activates the one that contains the partial name. Here's a sample VBA code to do this: Sub Example_2() ' Declare variables. Dim partialName As String.

  17. Range.Activate method (Excel)

    This example selects cells A1:C3 on Sheet1 and then makes cell B2 the active cell. Worksheets("Sheet1").Activate Range("A1:C3").Select Range("B2").Activate Support and feedback. Have questions or feedback about Office VBA or this documentation?

  18. Excel VBA Activate Workbook: Examples to Activate Workbooks

    In this example (only) 1 workbook ("Excel VBA Activate Workbook") is open. This is (also) the active workbook. When the macro is executed, Excel opens and activates the workbook with filename "Excel VBA Open and Activate Workbook.xlsx" stored in the Desktop folder. Quickly learn how to activate workbooks using macros.

  19. vba

    Could even set a reference to the sheet, which will make life easier later: Dim wkb As Workbook. Dim sht As Worksheet. Set wkb = Workbooks.Open("Tire.xls") Set sht = wkb.Sheets("Sheet2") sht.Activate. sht.Cells(2, 1) = 123. Others have pointed out that .Activate may be superfluous in your case. You don't strictly need to activate a sheet before ...

  20. Workbook.ActiveSheet property (Excel)

    In this article. Returns a Worksheet object that represents the active sheet (the sheet on top) in the active workbook or specified workbook. Returns Nothing if no sheet is active.. Syntax. expression.ActiveSheet. expression A variable that represents a Workbook object.. Remarks. Using the ActiveSheet property without an object qualifier returns the active sheet in the active workbook in the ...

  21. VBA Activate Workbook (Excel File)

    Steps to Activate a Workbook. Type "Workbooks" to use the workbook object. Specify the workbook name in the double quotation marks. Enter a dot (.) to get the list of properties and methods. Select the Activate method from the list or you can also type it. In the end, run the code to activate the workbook.

  22. How to share a dotm file with VBA with design mode off

    How to share a dotm file with VBA with design mode off. Hello all, I have created a tool using word and VBA where a user selects the name of a company and it creates a letterhead based on data in an excel sheet. Briefly, there are three files involved. 1) The creator tool - a word document with a create letterhead button which loads the name of ...