The basic building block of Excel starts with the Workbook. The Workbook contains Worksheets.
All work in Excel is performed in a Worksheet within a Workbook.
The Worksheet is the workhorse of Excel.
A Worksheet is part of the collection of Worksheets which is all the Worksheets in a particular Workbook.
Like the Workbooks Collection, the Worksheets Collection is only used to identify a Worksheet to be Created or Acted Upon.
The Worksheets Collection has only a few Methods, the primary ones being: Add, Copy, Delete, Move, and Select.
The Worksheets Collection has very few Properties. The ones you will use most are Count and Item.
We will reference the Worksheets Collection in many of the following examples.
Assign a variable name for Worksheets.
This example has a Workbook (named "IncomeStatement" with two Worksheets, named "Revenue Summary" and "Expense Summary". [The name of the worksheet is the name on the bottom tab. ]
We need to declare variables for the Workbook, the two Worksheets, and a third Worksheet that we will create.
Dim wkb as Workbook
Dim rev as Worksheet
Dim exp as Worksheet
Dim wst as Worksheet
SET the variables to specific worksheets.
To assign the variables to the worksheets, you must reference the workbook they are in. If there is only one workbook open you can use: Set wkb = Workbooks.ActiveWorkbook. There are many applications where you will have multiple workbooks open. In those cases, use the name of the workbook file. The VBA code has to know which workbook contains the worksheets you are referencing.
Add a new worksheet
This example uses the variable for the worksheet and adds it to the worksheets collection for the workbook variable wkb
The worksheet is renamed right after it is created. This will change the name on the bottom tab.
Set a variable to a worksheet and Rename it.
The worksheet has a property called .Name. This example updates the name of the worksheet.
Remember, when a worksheet is created it is given a name like Sheet1 or Sheet2, depending on how many sheets are already created.
The tab on Sheet1 will now say "Sales Summary".
SET the worksheet variable with the worksheet index.
You can use the worksheet index number to assign the worksheets to variables.
Beware, however, that you cannot always tell which sheet has which index number. Excel will assign index numbers as worksheets are created or deleted. It is much safer to use the worksheet name.
Reading through all worksheet names
Worksheets.Count returns the number of worksheets in the workbook.
Actually it is a count of the worksheets collection in the workbook.
This loop can be used to process all the worksheets in a workbook.
[SEE: For i = 1 to #number syntax]
The above example can find a specific worksheet when you don't know its index number.
The variable i will contain the index number for each selected sheet.
There are many uses for going through the list of all worksheets.
Copy or Move a Worksheet
Copy will create a new worksheet that is a duplicate of an exiting sheet.
Move will place and existing worksheet in another location.
Before and After are used to specify the name of the worksheet where the moved or copied worksheet will be placed.
SYNTAX: [Worksheet].Copy(Before, After)
Before:OPTIONAL - Name of the worksheet to move or copy in front of.
After:OPTIONAL - Name of the worksheet to move or copy in back of.
'Using the worksheet names used above:
wkb.Worksheets("Revenue Summary").Copy after:=wkb.Worksheets("Sales Summary")
wkb.Worksheets("Expense Summary").Move before:=wkb.Worksheets("Revenue Summary")
Since we have set variable names for the Worksheets, we can simplify our code by writing:
'Using the worksheet variable names:
Now, there will be a copy of the Revenue Summary sheet after the Sales Summary Sheet. Excel will call it Revenue Summary (2). The Expense Summary sheet was moved in front of Revenue Summary. There are now four(4) worksheets in the workbook.
Before and After are optional. If you don't specify either, the Moved or Copied worksheet will be placed in a new workbook.
Delete A Worksheet
In addition to Copy and Move, you can Delete a worksheet. The syntax is very simple:
'Using the worksheet variable name: