VBA Worksheet Methods

The VBA Worksheet Methods represents a worksheet in a workbook. You can use the Worksheets collection to access all the Worksheet objects in a workbook. Here are some of the methods that you can use with the Worksheet object:

This article includes following topics
ActivateUsed to Activate a Worksheet​
CalculateUsed to Refresh the calculations of Worksheet​
CopyUsed to Copy a Worksheet​
DeleteUsed to Delete a Worksheet​
MoveUsed to Move a Worksheet​
ProtectUsed to Protect a Worksheet​
SelectUsed to Select a Worksheet​
UnprotectUsed to Unprotect a Worksheet​
AddUsed to Add new Sheet​
NameUsed to Add New Sheet with Name, Get name of Sheet, Rename Name​
TabUsed to Change Tab Color​
HideUsed to Hide Sheet​
UnhideUsed to Unhide Sheet​
CountUsed to count to no of sheets on current workbook

Find the examples below given.

\' How to Create new sheet?
Sub Create_Sheet()
Sheets.Add
End Sub

\' How to Create new sheet with new name?
Sub Create_Sheet_name()
Sheets.Add
ActiveSheet.Name = \"Welcome\"
End Sub

\' How to Create new sheet with new name?
Sub Create_Sheet_name_short()
Sheets.Add.Name = \"Welcome\"
End Sub

\' How to Create new sheet with new name along with date?
Sub Create_Sheet_name_Date()
Sheets.Add.Name = Format(Now(), \"dd/mm/yyyy\")
\'Sheets.Add.Name = Format(Now(), \"mm\")
End Sub

\' How to added new sheet before/after particular sheet?
Sub add_sheet_after()
Sheets.Add After:=Worksheets(\"Sheet1\")
\'Sheets.Add Before:=Worksheets(\"Sheet1\")
End Sub

\' How to added new sheet before/after particular sheet with new name?
Sub add_sheet_after_name()
Sheets.Add(After:=Worksheets(\"Sheet1\")).Name = \"Welcome\"
End Sub

\'Giving name to sheets by numbering like sheet(1), sheet(2)?
\' in this methods we have added sheets before then give new name by there sheet number.
\' sheet number will be from left to right even we have given name to sheets it will change name
\' accordingly

\' How to give sheet new name according to sheet index?
Sub rename_sheet_index()
Sheets(2).Name = \"2023\"
End Sub

\' How to give Rename a sheet?
Sub rename_sheet()
Worksheets(\"Welcome\").Name = 2024
End Sub

\' Find how many sheets we have in current book?
Sub count_sheets()
\'MsgBox Sheets.Count
Range(\"A1\").Value = Sheets.Count
End Sub

\' How to give tab color?
Sub tab_color()
Worksheets(\"Data\").Tab.Color = vbRed
End Sub

\'How to calculate/update sheets data?

Sub calculate_data()
Sheets(\"data\").Range(\"E3:E9\").Calculate
End Sub

\' How rename exisitng file?
Sub rename_file()
Sheets(\"Data\").Name = \"New data\"
End Sub

Sub protect_data()
\'Sheets(\"new data\").Protect
Sheets(\"new data\").Unprotect
End Sub

Sub Hide_sheet_visibility()
\'Sheets(\"sheet2\").Visible = False
\'Sheets(\"sheet2\").Visible = xlSheetHidden \' xlsheethidden/xlsheetvisible
 Sheets(\"sheet2\").Visible = xlVeryHidden
End Sub

Sub copy_sheets()
Sheets(\"New data\").Copy After:=Worksheets(\"sheet4\")
End Sub

\' Move sheet after another sheet
Sub move_sheets()
Sheets(\"New data\").Move After:=Worksheets(\"sheet4\")
End Sub

Sub delete_sheet()
Sheets(\"new data (2)\").Delete
End Sub