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 |
|
| 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) |
| 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 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.
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!