VBA Worksheet Object in Excel

VBA Worksheet Object in Excel

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”)
Copy worksheet Set oWorksheet = Worksheets(“Sheet1”)
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)
Add New Worksheet After Specific Worksheet Worksheets.Add(after:=Sheets(“SpeccifySheetName”)).Name = “SheetName”
Add New Worksheet Before Specific Worksheet Worksheets.Add(before:=Sheets(“SpeccifySheetName”)).Name = “SheetName”
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
Delete worksheet Set oWorksheet = Worksheets(“Sheet1”)
Delete worksheet without prompt Application.DisplayAlerts = False
Application.DisplayAlerts = True

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

1 thought on “VBA Worksheet Object in Excel”

  1. 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