VBA Guide To Referencing Worksheet Tabs

By Chris Newman •  Updated: 05/27/17 •  2 min read
VBA Worksheet Reference Guide

Referencing Worksheets in Excel

When you are working with VBA inside Excel, it is more than likely that you will be automating some sort of changes to a worksheet inside your file. The following VBA guide is intended to show you have your can target specific worksheets within your workbooks in order to apply changes to them.

Reference Worksheet By Code Name [BEST PRACTICE!]

Sheet1.Range("A1").Value = 100

Reference Worksheet By Name

ThisWorkbook.Worksheets("Summary Tab").Range("A1").Value = 100

Reference Currently Viewed Worksheet

ActiveSheet.Range("A1").Value = 100

Reference Worksheet By Position

ThisWorkbook.Worksheets(3).Range("A1").Value = 100

Reference Last Worksheet In Workbook

ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Range("A1").Value = 100

Reference Worksheet Within Another Workbook

Workbooks("Book2").Worksheets("Sheet1").Range("A1").Value = 100

Store Worksheet To A Variable

Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("Summary Tab")

Store Newly Created Worksheet To A Variable

Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets.Count)

Loop Through Every Worksheet In A Workbook

Sub WorksheetLoop()

Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
  sht.Range("A1").Value = 100

Next sht

End Sub

Loop Through Every Worksheet In Reverse Order

Sub WorksheetReverseLoop()

Dim x As Long

'Reverse Loop Through Sheets
  For x = ThisWorkbook.Worksheets.Count To 1 Step -1
    ThisWorkbook.Worksheets(x).Range("A1").Value = 100
  Next x

End Sub

Loop Through A List Of Worksheet Names

Sub WorksheetListLoop()

Dim SheetList As Variant

'List Sheet Names into an Array Variable
  SheetList = Array("Sheet1", "Sheet4", "Sheet6")

'Loop through list
  For x = LBound(SheetList) To UBound(SheetList)
    ThisWorkbook.Worksheets(SheetList(x)).Range("A1").Value = 100
  Next x

End Sub

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.