Always assign a
variable name for
worksheets.
This allows you to reference all the worksheets in a workbook.
Dim rev as Excel.Worksheet
Dim def as Excel.Worksheet
SET the variable to a specific worksheet.
[The name of the worksheet is the name on the bottom tab.]
Set rev=Worksheets("Revenue Summary")
Set def=Worksheets("Deferred Income")
[Note that worksheets are created out of the worksheets collection.
All workbooks have a collection of worksheets.]
Set a variable to a worksheet and rename it.
Set rev=Worksheets("Sheet1")
rev.Name = "Revenue Summary:"
The tab on Sheet1 will now say "Revenue Summary"
You can also
SET the worksheet variable with the
worksheet index.
Set rev=Worksheets(1)
Set def=Worksheets(2)
Beware, however, that you cannot always tell which sheet has which index number.
Set a variable to the worksheet of a specific workbook.
The following examples assume that the worksheets belong to the Workbook with a variable of
wkb.
Set rev=wkb.Worksheets(1)
Set def=wkb.Worksheets(2)
See the article
Programming Excel Workbooks with VBA
Reading through all worksheet names
Worksheets.Count returns the number of sheets in the workbook.
Actually it is a count of the worksheets collection in the workbook.
For i = 1 To wkb.Worksheets.Count
If Worksheets.Item(i).Name = "Sheet1" then
Set rev = Worksheets("Sheet1")
End If
Next i
This loop can be used to process all the worksheets in a workbook, or to find a specific worksheet when you don't know its index number.
In the above example, the variable
i will contain the index number for the selected sheet.
Renaming a sheet
This changes the name on the worksheet bottom tab.
Set jvb = wkb.Worksheets("Sheet1")
jvb.Name = "Journal Vouchers"
Add a NEW sheet and RENAME it
Set jvb = wkb.Worksheets.Add
jvb.Name = "Journal Vouchers"
If you add a sheet, but do not rename it, it will have the name
Sheet4 or whatever the next available sheet number is.