VBA Worksheet Object

Worksheet Object | Referencing a Sheet | CodeName | Create New Worksheet | Delete Worksheet | Name Sheet | Reorder Sheets | Print | Declare Worksheet Object

Worksheet Object

You may have noticed I introduced the Worksheet Object in my Workbook Object example. The Worksheet Object represents the worksheets in your file. Sheet1, Sheet2 and Sheet3 are examples of Worksheet names and are the default names of a new workbook.

Much like the Workbook Object, the Worksheet Object is a member of the Worksheets Collection, so get used to seeing it written in plural.

Referencing Worksheets

You can refer to your worksheets in many ways. The following macros are all equivalent. Each one places a “5” in cell “A2” of worksheet “Demo,” which is the second sheet in the Excel Workbook.

Example 1

Sub WorksheetDemo()
Worksheets("Demo").Range("A2") = 5
End Sub

Example 2

Sub WorksheetDemo()
Sheets("Demo").Range("A2") = 5
End Sub

Example 3

Sub WorksheetDemo()
Sheet2.Range("A2") = 5
End Sub

Example 4

Sub WorksheetDemo()
Worksheets(2).Range("A2") = 5
End Sub

By now you’re probably starting to notice a theme; VBA offers several ways of performing the same task. The best part is that worksheet “Demo” does not have to be selected for VBA to know where to place the “5!” You’ll find this adds a lot of flexibility to what you’re able to perform in VBA.

Continue reading the CodeName Property section to understand how Example 3 works.

CodeName Property

In Example 3, Sheet2 is the CodeName of Worksheet “Demo.” How would you know this? Just check your VBA Editor. The first name of each worksheet under Microsoft Excel Objects is the CodeName. The second is the sheet name.

You can also return the CodeName of your Worksheet with the CodeName Property.

Sub WorksheetDemo()
MsgBox (Worksheets("Demo").CodeName)
End Sub

Result:

Create New Worksheet

Creating a new worksheet is simple with the Worksheets.Add Method.

Sub WorksheetDemo()
Worksheets.Add
End Sub

Result:

Delete Worksheet

The following VBA example will delete worksheet “Sheet1.”

Sub DeleteWorksheet()
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True
End Sub

The Application.DisplayAlerts property prevents the following message from displaying:

Name Sheet

You can rename a worksheet with the Worksheet.Name Property

Sub WorksheetDemo()
Worksheets("Sheet4").Name = "Disney"
End Sub

Result:

Reorder Worksheets

Let’s put it all together. Say you want to add a new sheet named “Vacation” and you want it placed at the end of your workbook. The following code does just that.

Sub WorksheetDemo()
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "Vacation"
End Sub

Result:

Print Worksheet

To print an entire worksheet, use the Printout Method just like you would print a range!

Sub WorksheetDemo()
Worksheets("Vacation").Printout
End Sub

Declare Worksheet Object

This is where you come in. I want you to try this one yourself. Remember how we declared Workbook Objects in the last lesson? I want you to do the same with the Worksheet object and play around until you get comfortable with it! The format is almost identical. I’ll even get you started with a hint:

Sub WorksheetDemo()
Dim sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")
sh1.Range("A1") = "Hello World"
End Sub

Declaring your worksheet objects like this shortens your code and reduces errors, so why not practice until you get good at it!

Today you learned about the Worksheet Object. You now know 4 different ways of referring to a worksheet in your VBA code. You learned how to add a sheet, rename a sheet and reorder your sheets. You also learned how to print an entire worksheet.

I hope it’s all starting to come together. Shoot me a message if you have a question about anything you’ve learned so far. I’m here to help you.

If you really want to learn VBA, grab a copy of our Ultimate VBA Training Bundle before reading our next tutorial. We specifically created these cheat sheets to help you get the most out of our upcoming lessons. Together, the set has over 200 practical tips covering the 125 most important topics in Excel VBA. We also stuffed it with 140 helpful macro examples.