Activate Workbook Or Worksheet In Excel VBA - ANALYSISTABS.COM

Home » Activer Fichier Excel Vba » Activate Workbook Or Worksheet In Excel VBA - ANALYSISTABS.COM

Maybe your like

Skip to content Toggle Navigation

120+ Professional Project Management Templates!

Save Up to 85% LIMITED TIME OFFER

View DetailsAnalysistabs Logo Search for: Go to... Home » Excel VBA » Activate Workbook Or Worksheet in Excel VBAPreviousNextExcel VBAActivate Workbook Or Worksheet in Excel VBAExcel VBA Examples|

Share This Post

Activate Workbook or Worksheet In This Article
    • Description:
  • Activate Workbook Or WorkSheet with VBA- Example Cases:
VBA Code Library
  • VBA Code Explorer
  • Application
    • Methods
      • Calculate
      • CalculateFull
      • FindFile
      • Goto
      • Run
      • Wait
    • Properties
      • Calculation
      • Caption
      • DisplayAlerts
      • DisplayFormulaBar
      • DisplayFullScreen
      • DisplayScrollBars
      • DisplayStatusBar
      • EnableEvents
      • Path
      • ScreenUpdating
      • StatusBar
      • UserName
      • WindowState
  • Workbook
    • Methods
      • Activate
      • Close
      • Protect
      • ProtectSharing
      • RefreshAll
      • RejectAllChanges
      • RemoveUser
      • RunAutoMacros
      • Save
      • SaveAs
      • SaveCopyAs
      • SendMail
      • Unprotect
      • UnProtectSharing
  • Worksheet
    • Methods
      • Activate
      • Calculate
      • Copy
      • Delete
      • Move
      • Protect
      • Select
      • Unprotect
  • Excel VBA Range
    • Methods
      • Activate
      • AddComment
      • AdvancedFilter
      • Calculate
      • Clear
      • ClearComments
      • ClearContents
      • ClearFormats
      • ClearHyperlinks
      • Copy
      • CopyFromRecordset
      • Cut
      • Delete
      • Excel VBA PasteSpecial
      • Find
      • Insert
      • ListNames
      • Merge
  • ActiveX Controls
    • CheckBox
      • Properties
        • Alignment
        • BackColor
        • Caption
        • Enabled
        • Height
        • Left
        • TextAlign
        • Top
        • Width
    • ComboBox
    • CommandButton
    • Image
    • Label
    • ListBox
      • Properties
        • BackColor
        • BorderStyle
        • ColumnCount
        • Enabled
        • Height
        • Left
        • MultiSelect
        • TextAlign
        • Top
        • Width
    • OptionButton
      • Caption
    • TextBox
  • Userform
    • CheckBox
    • ComboBox
    • CommandButton
    • Image
    • Label
    • ListBox
    • OptionButton
    • TextBox
    • Tutorial & Examples
  • Projects
    • Calculator using Excel VBA UserForm
    • Data Entry Userform
    • Excel VBA UserForm: Difference Between Two Dates
    • TOC creator in Excel Workbook using VBA
    • VBA List Folders Subfolders in Directory
    • VBA to Append Data from multiple Excel Worksheets into a Single Sheet – By Column
    • VBA to Consolidate data from multiple Excel Worksheets into a Single Sheet – By Row

REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

View Details

120+ PROFESSIONAL

Project Management Templates

View Details
120+ PM Templates Includes:

  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

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

Share Post

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.

Code:
Sub Activate_Workbook() 'Activate Workbook Workbooks("Book2.xls").Activate 'Activate Worksheet Workbooks("Book2.xls").Sheets("Sheet1").Activate End Sub
Output:

VBA Activate Workbook Or Work Sheet

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see Aactivated workbook and Worksheet
  7. 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.

Code:
Sub Activate_Workbook_Using_Object() 'Variable Declaration Dim WrkBk As Workbook Dim WrkSht As Worksheet 'Create Object for Workbook Set WrkBk = Workbooks.Add 'Create Object for Worksheet Set WrkSht = WrkBk.Sheets("Sheet1") 'Activate Sheet WrkSht.Activate End Sub
Explanation:
  1. We declared two objects for workbook and worksheet
  2. We have added a new workbook and assigned to workbook object
  3. We set the Worksheet to worksheet object
  4. We activated the worksheet by refering the worksheet object

    5. .

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see output as shown above
  7. Save the file as macro enabled workbook
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.

Code:
Sub Activate_Workbook_Using_Object() 'Variable Declaration Dim wbAs Workbook Dim wsAs Worksheet 'Select any Workbook and assign to an Object Set wb= Workbooks("Book1.xlsm") 'Create Object for Worksheet Set ws= wb.Sheets("SheetName") 'VBA to Select or Activate the required Workbook wb.Activate 'VBA to Select or Activate the required Sheet ws.Activate End Sub Effortlessly Manage Your Projects and Resources120+ Professional Project Management 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
Browse All TemplatesProject Management Templates

All-in-One Pack 120+ Project Management Templates

120+ PM Templates

Essential Pack 50+ PM Templates

50+ PM Templates

Excel Pack 50+ Excel PM Templates

50+ Excel Templates

PowerPoint Pack 50+ Excel PM Templates

50+ PPT Templates

MS Word Pack 25+ Word PM Templates

25+ Word Templates
Ultimate Project Management Template
Ultimate PM Template
Ultimate Resource Management Template
Resource Management
Project Portfolio Management Templates
PPM Templates

Related Posts

VBA ActiveSheet – Excel Active Sheet Object VBA ActiveSheet – Excel Active Sheet Object Gallery

VBA ActiveSheet – Excel Active Sheet Object

August 6, 2019|0 Comments Excel VBA ColorIndex Excel VBA ColorIndex Gallery

Excel VBA ColorIndex

July 6, 2019|0 Comments Excel VBA Copy Range to Another Sheet with Formatting Excel VBA Copy Range to Another Sheet with Formatting Gallery

Excel VBA Copy Range to Another Sheet with Formatting

July 5, 2019|1 CommentPublished On: January 12, 2013Categories: Excel VBATags: Excel VBA ExamplesLast Updated: June 17, 2022

About the Author: PNRao

Hi, I’m PNRao—an Excel & VBA developer with 20 years in data mining, automation, and project management. Day-to-day I turn raw data into clear insight, replace repetitive work with one-click workflows, and guide teams with smarter project management. On Analysistabs.com I share battle-tested tips on Excel, VBA, SQL, Automation, Project Management, and Data Analysis—plus a growing library of free and premium Project Management Templates. My goal is to help you work faster, build sharper tools, and level up your career. Let's master data and manage projects effectively, together.

3 Comments

  1. Jude September 15, 2014 at 11:20 PM - Reply

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

  2. PNRao September 25, 2014 at 8:25 PM - Reply 'You can use in same way: Workbooks(“Book2.xls”).Sheets("Sheet 1").Activate

    Thanks-PNRao!

  3. Ryan August 16, 2016 at 7:13 AM - Reply

    Hey Valli,

    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!

    Regards

Leave A Comment Cancel reply

Comment

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

Effectively Manage Your Projects and  Resources

With Our Professional and Premium Project Management Templates!View Details

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

Excel VBA

Download Free Excel 2007, 2010, 2013, 2016, 2019 + Microsoft Office 365 Templates, Dashboards, Tools for Project Management, Data Mining, Analysis, Visualization and VBA Automation. Learn Project Management and VBA for Microsoft Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Analysistabs Logo

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

Page load link Go to Top

Tag » Activer Fichier Excel Vba