In this article I will explain how to work with the worksheet object. One the most important thing to know when working with worksheets is understanding the difference between the items below:

• Sheet Object’s Variable Name
• Sheet Name
• Sheet Index

I will also explain how to copy and create new worksheets programmatically.

Jump to:

• Referencing Sheets by Using the Sheet Object Directly
• Sheet Name
  • Referencing Sheets Using Their Name
• Sheet Index
  • Referencing Sheets Using Their Index
• Creating New Worksheets
  • Creating and Inserting Sheets After a Specific Sheet (:=After)
  • Creating and Inserting Sheets Before a Specific Sheet (:=Before)
  • Inserting Multiple Sheets (:=Count)
  • Inserting different types of sheets (:=Type)
• Copying Sheets

Referencing Sheets by Using the Sheet Object Directly:

From an object oriented programming perspective, each sheet is an object. The address of each object is stored in a variable.  To figure out what variable references each sheet you could use the project explorer in the VBA editor. The highlighted part in the figure below shows the name of the sheet objects variables:

The table below shows the variable associated with each sheet:

To reference cells and ranges using the sheet object directly you could follow the sample codes below:

Sub Example1()
Sheet1.Cells(1, 1) = "Some Data"
Sheet2.Range("A1") = "some Data"
Sheet3.Range("A1:A1") = Sheet2.Range("A1")
End Sub


Note the only way to modify the name of the sheet objects variable is through the property window in the VBA editor:

As you can see in the table below Sheet2‘s object’s name has been change to Object_Sheet2:

By changing the name of sheet2‘s object to Object_Sheet2 the same line of code above will become:

Sub Example2()
Sheet1.Cells(1, 1) = "Some Data"
'note the change in this line
Object_Sheet2.Range("A1") = "some Data"
Sheet3.Range("A1:A1") = Sheet2.Range("A1")
End Sub

Sheet Name:

The sheet names are the values shown in the bottom panel of the excel workbook:

As you can see this workbook has three sheets with the names “Sheet1”, “New Name for Sheet3” and “Sheet2”. You could also see the sheet names using the project window. The values in the parenthesis:

Below you can see the sheet object’s names and the sheet names:

In order to change the sheet name there are 3 methods:

Method 1: Using the property window:

Method 2: Renaming the sheet using the panel at the bottom of the workbook:

Method 3: Unlike the sheet object variables name which could only be changed using the property window, you can change the sheets name programmatically:

Sub Example3()
Sheet1.Name = "Changing Sheet1's Name"
End Sub

After making the change to sheet1’s name, we will have:

 

Referencing Sheets Using Their Name:

You could also reference a sheet using the sheet name and the WorkSheets collection. In the example below there are 3 sheets with the names  “MySheet1”, “MySheet2” and “MySheet3”. They are referenced using the sheet names through the WorkSheets collection.

Sub Example4()
Sheets("MySheet1").Cells(1, 1) = "some Data"
'sheets and worksheets are the same
Worksheets("MySheet2").Range("A1") = "some data"
Sheets("MySheet3").Range("A1:A1") = Worksheets("MySheet2").Range("A1")
End Sub

The syntax for referencing sheets using their name is Sheets(“Sheet Name”) and Worksheets(“Sheet Name”). Both return the same results.

Note: Keep in mind that the sheet name could be modified by the user. This may cause errors in your code if your program references sheets using their name. You might need to consider locking to workbook from such changes.

Sheet Index:

Each sheet is associated an index value, starting from the value “1”. The index value depends on the order of the sheets. See the example below:

In the picture above the indexes and sheet names are as follows:

By moving the sheets around the indexes will also change. See the example below:

Referencing Sheets Using Their Index:

You could also reference a sheet using the sheet index and the WorkSheets collection. In the example below there are 3 sheets:

They are referenced using the sheet index through the WorkSheets collection:

Sub Example5()
Sheets(1).Cells(1, 1) = "some Data"
'sheets and worksheets are the same
Worksheets(2).Range("A1") = "some data"
Sheets.Item(3).Range("A1:A1") = Worksheets.Item(2).Range("A1")
End Sub


Note: Keep in mind that the user can change the order of the sheets. This may cause error in your code if you are referencing sheets using their index. You might need to consider locking the workbook from such changes.

Creating New Worksheets:

In the following examples I will assume I have 3 sheets, with the attributes below:

Creating and Inserting Sheets After a Specific Sheet (:=After):

The code below add a sheet after sheet3. Note all the samples yield the same result:

'using sheet object
Call Worksheets.Add(after:=Sheet3)

'using sheet name
Call Worksheets.Add(after:=Worksheets("MySheet3"))

'using sheet name
Call Worksheets.Add(after:=Sheets("MySheet3"))

'using sheet index
Call Worksheets.Add(after:=Worksheets.Item(3))

'using sheet index
Call Worksheets.Add(after:=Sheets.Item(3))

Creating and Inserting Sheets Before a Specific Sheet (:=Before):

The code below add a sheet before sheet2 and names it “MySheet4”. Note all the samples yield the same result:

Dim wrkSheet_Temp As Worksheet

'using sheet object
Set wrkSheet_Temp = Worksheets.Add(before:=Sheet2)
wrkSheet_Temp.Name = "MySheet4"
'using sheet name
Set wrkSheet_Temp = Worksheets.Add(before:=Worksheets("MySheet2"))
wrkSheet_Temp.Name = "MySheet4"
'using sheet name
Set wrkSheet_Temp = Worksheets.Add(before:=Sheets("MySheet2"))
wrkSheet_Temp.Name = "MySheet4"
'using sheet index
Set wrkSheet_Temp = Worksheets.Add(before:=Worksheets.Item(2))
wrkSheet_Temp.Name = "MySheet4"
'using sheet index
Set wrkSheet_Temp = Worksheets.Add(before:=Sheets.Item(2))
wrkSheet_Temp.Name = "MySheet4"

Inserting Multiple Sheets (:=Count):

The codes below insert 5 new sheets after the last sheet:

Dim i As Integer
'using the count:= property
Call Worksheets.Add(after:=Worksheets.Item(Worksheets.Count), Count:=5)
'using Iteration
For i = 1 To 5
    Call Worksheets.Add(after:=Worksheets.Item(Worksheets.Count))
Next i


Inserting different types of sheets (:=Type):

There are 4 types of sheets you can add, xlWorksheet, xlChart, XlExcel4MacroSheet and xlExcel4IntlMacroSheet:

Sub example6()
'adds a sheet of type worksheet
Call Worksheets.Add(Type:=xlWorksheet)
'adds a sheet of type chart
Call Worksheets.Add(Type:=xlChart)
'adds a sheet of type macro
Call Worksheets.Add(Type:=xlExcel4MacroSheet)
'adds a sheet of type macro
Call Worksheets.Add(Type:=xlExcel4IntlMacroSheet)
End Sub


Copying Sheets:

Copying sheets is similar to creating sheets. I will assume I have the same set of sheets as the example I’ve used in Creating New Worksheet:

Similar to creating new sheets you could choose to copy after or before a certain sheet using the :=after and :=before attributes. For more information regarding the :=after and :=before attributes please see Creating New Worksheet. The code below copies sheet1 after sheet3 using the sheet name:

'Using the sheet name
Call Worksheets("MySheet1").Copy(after:=Sheet3)


The code below copies sheet1 before sheet2 using the sheet index:

'using the sheet index
Call Worksheets(1).Copy(before:=Worksheets("MySheet3"))


The code below copies sheet1 after sheet2 using the sheet object:

'using the sheet object
Call Sheet1.Copy(after:=Sheets("MySheet2"))


If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com