• • • • • • • Why we need to activate a Workbook using VBA? When we deal with multiple workbooks and if you want to read or write to a specific workbook. You can easily activate a workbook using VBA. And do whatever tasks which you want to do. VBA Activate Workbook – Syntax Here is the example syntax to activate a Workbook using VBA. You can use either a Workbook name or Workbook number. When we specify the workbook number if it the order of the workbooks which you are opening.

Workbook Object [Excel 2003 VBA Language. Where index is the workbook name or index. The following example sets the name of the author for the active workbook. VBA Activesheet vs Worksheets. Excel VBA Worksheets VBA Activesheet vs Worksheets, Select, Activate etc. Worksheet VBA Name vs Excel Name.

Jul 27, 2012 Hi. I am trying to activate an open workbook that has a variable name. I get subscript out of range error. What do I need to change? Please see below. Feb 08, 2015 Excel 2013 VBA 7. Snow Patrol A Hundred Million Suns Rar there. 1 Workbook Activate. Workbooks('Name of.

Workbooks(“Your Workbook Name”).Activate ‘Or Workbooks(. [Workbook Number]).Activate ‘And you can use Thisworkbook.Activate method to activate the workbook with the current procedure/macro ThisWorkbook.Activate VBA Activate Workbook – with Name:Example 1 Please see the below VBA codes to activate a Workbook. In this example we are activating a workbook named “Project1”. 'Workbook Activate Sub sb_Activate_Workbook() Workbooks('Project1').Activate End Sub VBA Activate Workbook – with Number:Example 2 Please see the below VBA codes to activate a Workbook using workbook number. In this example we are activating a workbook 2 in the currently opened workbooks collection. 'Workbook Activate Sub sb_Activate_Workbook_Number() Workbooks(2).Activate End Sub VBA Activate Workbook – ThisWorkbook:Example 2 Please see the below VBA codes to activate a currently running macro workbook. This will be very useful while dealing with the multiple workbooks.

Let’s say you have your macros in “MyProjects1.xlsm” and you have opened multiple workbooks say Book2.xlsx, Book3.xlsx, Book4.xlsx, and you can deal with any workbook and come back to your original workbook with the currently running code by just referring to ThisWorkbook Object. 'Workbook Activate Sub sb_Activate_Workbook_ThisWorkbook() 'Lets say you have written this macro in 'MyProjects1.xlsm 'And say you want to write to Book2 Workbooks('Book2').Activate Sheet1.Range('A1') = 1 'Now you want to write to Book3 Workbooks('Book3').Activate Sheet1.Range('A1') = 1 'You can come back to activate the currently macro running workbook ThisWorkbook.Activate 'This will activate 'MyProjects1.xlsm End Sub VBA Activate Workbook – Best Approach Note: Always better to use the Workbook name, instead of workbook number. The best is to assign the workbook to an object and then do whatever task you want to do with that particular Workbook object. When working with multiple workbooks, you should refer the workbook with exact workbook name to correctly update your data into target workbook.

Create workbook object and refer the workbook with the object whenever you require. Let us see the another example to understand the accessing the workbooks using objects. You do not need to activate workbook to deal with any workbook.

I am trying to activate an open workbook that has a variable name. I get 'subscript out of range' error. What do I need to change? Please see below, line right before the End Sub is where I get my error. Sub test() Dim myfile As Variant myfile = Application.GetOpenFilename(FileFilter:='Excel Files (*.xls), *.xls', Title:='Please select a file') Workbooks.Open Filename:=myfile 'Here I will do other stuff and flip to another workbook 'Here I want to go back to myfile, but I get subscript out of range error Workbooks(myfile).Activate End Sub. I am trying to activate an open workbook that has a variable name. I get 'subscript out of range' error.

What do I need to change? Please see below, line right before the End Sub is where I get my error. Sub test() Dim myfile As Variant myfile = Application.GetOpenFilename(FileFilter:='Excel Files (*.xls), *.xls', Title:='Please select a file') Workbooks.Open Filename:=myfile 'Here I will do other stuff and flip to another workbook 'Here I want to go back to myfile, but I get subscript out of range error Workbooks(myfile).Activate End Sub.