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