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
Activate	Used to Activate a Worksheet
Calculate	Used to Refresh the calculations of Worksheet
Copy	Used to Copy a Worksheet
Delete	Used to Delete a Worksheet
Move	Used to Move a Worksheet
Protect	Used to Protect a Worksheet
Select	Used to Select a Worksheet
Unprotect	Used to Unprotect a Worksheet
Add	Used to Add new Sheet
Name	Used to Add New Sheet with Name, Get name of Sheet, Rename Name
Tab	Used to Change Tab Color
Hide	Used to Hide Sheet
Unhide	Used to Unhide Sheet
Count	Used 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