Microsoft Office Tutorials and References
In Depth Information
When this code line is executed, the active workbook becomes the new workbook you added, same
as the effect of manually adding a new workbook from your existing one, when the workbook you
added becomes the active workbook.
What if your project calls for you to add two workbooks to the existing one, and you want to end
the macro with the first added workbook being the active one, instead of the last added workbook
being the active one? In your Workbooks collection, how do you specify which Workbook object you
want to do something with, when you don’t know the names of any open workbooks?
VBA offers several methods to solve this problem, one being an ability to assign a variable to each
workbook you add, and then to activate the workbook whose variable you care about. For example,
this macro will add two workbooks and end with the first added workbook being the active one:
Dim WorkbookAdd1 As Workbook
Dim WorkbookAdd2 As Workbook
Set WorkbookAdd1 = Workbooks.Add
Set WorkbookAdd2 = Workbooks.Add
Workbook objects have a number of methods as you would expect, such as Open , Save , and Close .
Lesson 9 delves into the practice of repeating actions with loops, but here’s a sneak peek at a loop
that saves and closes every workbook that is currently open in your copy of Excel, except for the
workbook you are working in. Notice what you don’t see, which is a concern about how many
workbooks are open, or what their names are; you only need to tell VBA to look for Workbook
objects in the Workbooks collection.
Dim wkb As Workbook
For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
The Worksheets collection allows you to refer to the Worksheets objects’ names or index
numbers, which is the numerical position of worksheets as you see their tabs in order from left to right.
Referring to names tends to be a safer practice, but as you saw with workbooks, and as you will
learn with looping techniques, a variable can be assigned to each Worksheets object to access all
worksheets without caring where they are in the workbook or what their tab names are.
Say you want to add a new worksheet, and give it the name Test1. No problem there, but now you
are asked to add the new worksheet such that its placement shall be the last (rightmost) worksheet
in the workbook. You have no idea how many sheets exist already. You don’t know the name of the
last worksheet in order to reference its location but even if you did know that today, there could
easily be a differently named worksheet in that index position tomorrow.