VBA Guide To Referencing Worksheet Tabs

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

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Remove A Specific Fill Color From Your Spreadsheet

What This VBA Code Does This VBA macro code will allow you to remove a specific fill color from your...

Copy Selected Slides Into New PowerPoint Presentation

What This VBA Code Does Sometimes I have a huge PowerPoint deck filled with data slides from all sorts of...

The VBA Guide To Interacting With Text (.Txt) Files

Automating Text Files With VBA Useful? Text files can be a very fast and simple way to read and store...

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.