Working with Worksheets in VBA
Automate Excel worksheet operations with VBA code
Quick Reference
ActiveSheet: Currently selected worksheet
Worksheets("Name"): Reference by name
Worksheets(1): Reference by index (1-based)
ThisWorkbook: Workbook containing the code
Referencing Worksheets
By Name:
Worksheets("Sheet1").Range("A1").Value = "Hello"
' Or use Sheets collection (same thing)
Sheets("Data").Range("B2").Value = 100
By Index:
' First worksheet (leftmost tab)
Worksheets(1).Range("A1").Value = "First sheet"
' Third worksheet
Worksheets(3).Select
Index changes if sheets are moved or deleted!
Active and This Worksheet:
' Currently active sheet
ActiveSheet.Range("A1").Value = "Active"
' Sheet containing the code
ThisWorkbook.Worksheets("Summary").Activate
Best Practice - Set Reference:
Sub UseWorksheetVariable()
Dim ws As Worksheet
Set ws = Worksheets("Data")
' Now use ws throughout code
ws.Range("A1").Value = "Test"
ws.Range("B1").Value = 100
MsgBox ws.Name
End Sub
Tip: Always qualify worksheet references to avoid confusion. Use Worksheets("Name") instead of assuming ActiveSheet.
Creating New Worksheets
Add New Sheet:
Sub AddNewSheet()
' Add at end
Worksheets.Add
' Add at beginning
Worksheets.Add Before:=Worksheets(1)
' Add after specific sheet
Worksheets.Add After:=Worksheets("Data")
End Sub
Add and Name:
Sub AddNamedSheet()
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "New Data"
ws.Range("A1").Value = "Created: " & Now
End Sub
Add Multiple Sheets:
Sub AddMultipleSheets()
' Add 5 sheets at once
Worksheets.Add Count:=5
End Sub
Create if Not Exists:
Function GetOrCreateSheet(sheetName As String) As Worksheet
Dim ws As Worksheet
' Try to find existing sheet
On Error Resume Next
Set ws = Worksheets(sheetName)
On Error GoTo 0
' If not found, create it
If ws Is Nothing Then
Set ws = Worksheets.Add
ws.Name = sheetName
End If
Set GetOrCreateSheet = ws
End Function
Deleting Worksheets
Delete with Confirmation:
Sub DeleteSheet()
' Excel shows confirmation dialog
Worksheets("OldData").Delete
End Sub
Delete Without Confirmation:
Sub DeleteSheetNoAlert()
Application.DisplayAlerts = False
Worksheets("OldData").Delete
Application.DisplayAlerts = True
End Sub
Warning: Always re-enable DisplayAlerts! Use error handling to ensure it's restored.
Safe Delete with Error Handling:
Sub SafeDeleteSheet(sheetName As String)
On Error GoTo ErrorHandler
' Check if sheet exists
Dim ws As Worksheet
Set ws = Worksheets(sheetName)
' Delete it
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Exit Sub
ErrorHandler:
Application.DisplayAlerts = True
If Err.Number = 9 Then ' Subscript out of range
MsgBox "Sheet '" & sheetName & "' not found."
Else
MsgBox "Error deleting sheet: " & Err.Description
End If
End Sub
Copying and Moving Worksheets
Copy Sheet:
Sub CopySheet()
' Copy to end
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
' Copy before specific sheet
Worksheets("Data").Copy Before:=Worksheets(1)
End Sub
Copy and Rename:
Sub CopyAndRename()
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "January Data"
End Sub
Move Sheet:
Sub MoveSheet()
' Move to end
Worksheets("Summary").Move After:=Worksheets(Worksheets.Count)
' Move to beginning
Worksheets("Cover").Move Before:=Worksheets(1)
End Sub
Copy to New Workbook:
Sub CopyToNewWorkbook()
' Creates new workbook with copied sheet
Worksheets("Report").Copy
' Active workbook is now the new one
ActiveWorkbook.SaveAs "C:\Reports\Monthly.xlsx"
End Sub
Worksheet Properties
Name Property:
Sub RenameSheet()
Worksheets("Sheet1").Name = "Sales Data"
' Get current name
MsgBox ActiveSheet.Name
End Sub
Visibility:
' Hide sheet
Worksheets("Data").Visible = xlSheetHidden
' Show sheet
Worksheets("Data").Visible = xlSheetVisible
' Very Hidden (can't unhide via UI)
Worksheets("Calculations").Visible = xlSheetVeryHidden
Tab Color:
' Set tab color
Worksheets("Summary").Tab.Color = RGB(255, 0, 0) ' Red
' Use color constants
Worksheets("Data").Tab.Color = vbYellow
' Clear color
Worksheets("Sheet1").Tab.ColorIndex = xlColorIndexNone
Protection:
' Protect sheet
Worksheets("Data").Protect Password:="secret123"
' Unprotect
Worksheets("Data").Unprotect Password:="secret123"
' Check if protected
If ActiveSheet.ProtectContents Then
MsgBox "Sheet is protected"
End If
Looping Through Worksheets
For Each Loop:
Sub LoopAllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
Debug.Print ws.Name
ws.Range("A1").Value = "Updated"
Next ws
End Sub
For Loop with Index:
Sub LoopByIndex()
Dim i As Integer
For i = 1 To Worksheets.Count
MsgBox "Sheet " & i & ": " & Worksheets(i).Name
Next i
End Sub
Process All Visible Sheets:
Sub ProcessVisibleSheets()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
' Do something with visible sheets
ws.Range("A1").Value = "Processed"
End If
Next ws
End Sub
Find Sheet by Name Pattern:
Sub FindSheetsByPattern()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "Data*" Then ' Starts with "Data"
Debug.Print "Found: " & ws.Name
End If
Next ws
End Sub
Common Worksheet Tasks
Count Total Sheets:
MsgBox "This workbook has " & Worksheets.Count & " sheets"
Activate Specific Sheet:
Worksheets("Summary").Activate
' Or
Worksheets(1).Select
Clear All Data:
Sub ClearSheetData()
Worksheets("Data").Cells.Clear ' Everything
Worksheets("Data").Cells.ClearContents ' Values only
Worksheets("Data").Cells.ClearFormats ' Formatting only
End Sub
Check if Sheet Exists:
Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sheetName)
On Error GoTo 0
SheetExists = Not ws Is Nothing
End Function
Sort Sheets Alphabetically:
Sub SortSheetsAlphabetically()
Dim i As Integer, j As Integer
For i = 1 To Worksheets.Count - 1
For j = i + 1 To Worksheets.Count
If Worksheets(i).Name > Worksheets(j).Name Then
Worksheets(j).Move Before:=Worksheets(i)
End If
Next j
Next i
End Sub
Best Practices
✓
Reference by name, not index: Sheet positions change, names are more reliable
✓
Use worksheet variables: Set ws = Worksheets("Name") for cleaner code
✓
Always restore DisplayAlerts: Use error handling to ensure it's re-enabled
✓
Check sheet existence: Before referencing by name
✓
Avoid Select and Activate: Work directly with worksheet objects
✓
Use meaningful names: "SalesData" not "Sheet1"
✓
Qualify workbook references: ThisWorkbook.Worksheets vs Worksheets