VBA Worksheet Object in MS Excel

Worksheet object in Microsoft Excel represents a Worksheets collection of the Workbook. The Worksheets collection contains all the worksheet object. Each worksheet consists of different objects like Ranges, cells, Charts, shapes, etc.

VBA Worksheet Object Complete Reference Tutorial

Let us see a complete guide on VBA Worksheet object. So let’s start learning about complete VBA Worksheet object explained with examples in this tutorial.

Task Description Action performed (Sample Example)
Worksheet Reference
  1. ActiveSheet Reference
  2. Reference Worksheet by Default Name
  3. Reference Worksheet by Index
  4. Reference Worksheet by Object
  5. Worksheet in Worksheet Collection Reference
  6. Reference Worksheet Explicitly(By Name)
Declare worksheet variable Dim oWorksheet As Worksheet
Define worksheet variable Set oWorksheet = Worksheets(“Sheet1”)
Create new worksheet Worksheets.Add
Add Multiple Worksheets(Lets assume 4 sheets) Worksheets.Add Count:=4
Create new worksheet with Name Set oWorksheet = oWorksheet.Worksheets.Add(Type:=xlWorksheet)
With oWorksheet
.Name = strName
End With
Edit/Change Worksheet Name Set oWorksheet = Worksheets(“Sheet1”)
oWorksheet.Name = “NewSheet”
Activate worksheet Set oWorksheet = Worksheets(“Sheet1”)
oWorksheet.Activate
Copy worksheet Set oWorksheet = Worksheets(“Sheet1”)
oWorksheet.Copy
Access worksheet by Index Worksheets(1)
Number of Worksheets Worksheets.Count
Add Worksheet at the beginning of sheets Worksheets.Add Before:=Worksheets(1)
Add Worksheet at the end of sheets Worksheets.Add after:=Worksheets(Worksheets.Count)
Copy Worksheet Before Specified sheet oWorksheet.Copy After:=Worksheets(“Sheet4”)
Copy Worksheet After Specified sheet oWorksheet.Copy Before:=Worksheets(“Sheet2”)
Show worksheet oWorksheet.Visible = xlSheetVisible
Hide worksheet oWorksheet.Visible = xlSheetHidden
Protect worksheet oWorksheet.Protect Password:=”12345″
UnProtect worksheet oWorksheet.Unprotect Password:=”12345″
Display All Worksheet Names For Each oWorksheet In Worksheets
Debug.Print oWorksheet.Name
Next
Delete worksheet Set oWorksheet = Worksheets(“Sheet1”)
oWorksheet.Delete
Delete worksheet without prompt Application.DisplayAlerts = False
oWorksheet.Delete
Application.DisplayAlerts = True

Back to VBA Tutorial

You may also like the other articles related to VBA.

Excel VBA Tutorial

1 thought on “VBA Worksheet Object in Excel”

  1. http://godaddy.com

    Hi there, just became alert to your blog through Google, and found that
    it is really informative. I am gonna watch out for brussels.
    I will appreciate if you continue this in future.
    Many people will be benefited from your writing. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *