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
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
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...
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.