Working with worksheets is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax.
Referencing worksheets from the workbook
'Reference a worksheet in another workbook Workbooks("WorkbookName.xlsx").Worksheets("SheetName").[other properties/actions] 'Reference a worksheet in the same workbook as the VBA code ThisWorkbook.Worksheets("SheetName").[other properties/actions] 'Reference a worksheet in the active workbook ActiveWorkbook.Worksheets("SheetName").[other properties/actions]
To use any of the following examples you will need to include the workbook reference, otherwise Excel will assume you want to use the ActiveWorkbook.
Selecting worksheets by name
'Select a worksheet by name
Worksheets("SheetName")
Select worksheets by position from left most worksheet
'Select worksheets by position from the left most worksheet
Worksheets(1) 'the first worksheet
Worksheets(2) 'the second worksheet
Adding worksheets
'Add a worksheet Worksheets.Add 'Add 4 worksheets Worksheets.Add Count:=4 'Add a worksheet infront of a worksheet Worksheets.Add Before:=Worksheets(3) 'Add a worksheet after a worksheet Worksheets.Add after:=Worksheets(3) 'Add a worksheet at the start Worksheets.Add Before:=Worksheets(1) 'Add a worksheet to the end Worksheets.Add after:=Worksheets(Worksheets.Count) 'Add a new worksheet and assign it to a variable Dim Ws As Worksheet Set Ws = Worksheets.Add 'Add a new worksheet and name it Dim Ws As Worksheet Set Ws = Worksheets.Add Ws.Name = "SheetName"
Delete worksheets
'Delete a worksheet Worksheets("SheetName").Delete 'Delete a worksheet without displaying an error message Application.DisplayAlerts = False Worksheets("SheetName").Delete Application.DisplayAlerts = True
Assign a worksheet to a variable
'Assign a worksheet to a variable Dim Ws as Worksheet Set Ws = Worksheets("SheetName")
Rename a worksheet
'Rename a worksheet
Worksheets("OldSheetName").Name = "NewSheetName"
Set the visibility status of a worksheet.
'Visible Worksheets("SheetName").Visible = xlSheetVisible 'Hidden Worksheets("SheetName").Visible = xlSheetHidden 'Invisible Worksheets("SheetName").Visible = xlSheetVeryHidden
For other examples of worksheet visibility check out these posts:
Count worksheets
'Count worksheets
Worksheets.Count
Copy/Move worksheets
'Copy worksheets Worksheets("SheetName").Copy 'Move a worksheet in front of another worksheet Worksheets("SheetName").Copy Before:=Worksheets("AnotherSheetName") 'Move a worksheet after another worksheet Worksheets("SheetName").Copy After:=Worksheets("AnotherSheetName") 'Copy multiple worksheets Worksheets(Array("SheetName", "SheetName2", "SheetName3")).Copy
Protect & Unprotect worksheets
'Protect a worksheet Worksheets("SheetName").Protect 'Protect a worksheet with a password Worksheets("SheetName").Protect "password" 'Unprotect a worksheet without a password Worksheets("SheetName").Unprotect 'Unprotect a worksheet without a password Worksheets("SheetName").Unprotect "password"
Using the Active worksheet
'Activate a worksheet Worksheets("SheetName").Activate 'Rename the activesheet ActiveSheet.Name = "SheetName"
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy

Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.