If you are new to VBA then this post is a great place to start. I like to break things down into simple terms and explain them in plain English without the jargon. Show
You can read through the post from start to finish as it is written in a logical order. If you prefer, you can use the table of contents below and go directly to the topic of your choice. Contents
A Quick Guide to the VBA WorksheetThe following table gives a quick run down to the different worksheet methods. Note: I use Worksheets in the table below without specifying the workbook i.e.Worksheets rather than ThisWorkbook.Worksheets, wk.Worksheets etc. This is to make the examples clear and easy to read. You should always specify the workbook when using Worksheets. Otherwise the active workbook will be used by default. Worksheets(4)Access the left most worksheetWorksheets(1)Access the right most worksheetWorksheets(Worksheets.Count)Access using worksheet code name(current workbook only)see Code Name section belowAccess using worksheet code name(other workbook)see Code Name section belowAccess the active worksheetActiveSheetDeclare worksheet variableDim sh As WorksheetAssign worksheet variableSet sh = Worksheets("Sheet1")Add worksheetWorksheets.AddAdd worksheet and assign to variableSet sh =Worksheets.AddAdd worksheet to first position(left)Worksheets.Add Before:=Worksheets(1)Add worksheet to last position(right)Worksheets.Add after:=Worksheets(Worksheets.Count)Add multiple worksheetsWorksheets.Add Count:=3Activate Worksheetsh.ActivateCopy Worksheetsh.CopyCopy after a worksheetsh1.Copy After:=Sh2Copy before a worksheetsh1.Copy Before:=Sh2Delete Worksheetsh.DeleteDelete Worksheet without warningApplication.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True Change worksheet namesh.Name = "Data"Show/hide worksheetsh.Visible = xlSheetHidden sh.Visible = xlSheetVisibleLoop through all worksheets(For)Dim i As Long For i = 1 To Worksheets.Count Debug.Print Worksheets(i).Name Next iLoop through all worksheets(For Each)Dim sh As Worksheet For Each sh In Worksheets Debug.Print sh.Name Next IntroductionThe three most important elements of VBA are the Workbook, the Worksheet and Cells. Of all the code your write, 90% will involve one or all of them. The most common use of the worksheet in VBA is for accessing its cells. You may use it to protect, hide, add, move or copy a worksheet. However, you will mainly use it to perform some action on one or more cells on the worksheet. Accessing the WorksheetIn VBA, each workbook has a collection of worksheets. There is an entry in this collection for each worksheet in the workbook. This collection is simply called Worksheets and is used in a very similar way to the Workbooks collection. To get access to a worksheet all you have to do is supply the name. ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Write To cell A1 In Sheet1,Sheet2 And Sheet3 ThisWorkbook.Worksheets("Sheet1").Range("A1") = "Hello World" ThisWorkbook.Worksheets("Sheet2").Range("A1") = "Hello World" ThisWorkbook.Worksheets("Sheet3").Range("A1") = "Hello World" End Sub ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub Hide WorksheetThe following examples show how to hide and unhide a worksheet ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible ' Hide from user access ThisWorkbook.Worksheets("Sheet1").Visible = xlVeryHidden ' This is the only way to make a xlVeryHidden sheet visible ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible Protect WorksheetAnother example of using the worksheet is when you want to protect it ThisWorkbook.Worksheets("Sheet1").Protect Password:="MyPass" ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="MyPass" Subscript Out of RangeWhen you use Worksheets you may get the error: Run-time Error 9 Subscript out of Range
Using the Index to Access the WorksheetSo far we have been using the sheet name to access the sheet. The index refers to the sheet tab position in the workbook. As the position can easily be changed by the user it is not a good idea to use this. ' https://excelmacromastery.com/ ' Using this code is a bad idea as ' sheet positions changes all the time Public Sub UseSheetIdx() With ThisWorkbook ' Left most sheet Debug.Print .Worksheets(1).Name ' The third sheet from the left Debug.Print .Worksheets(3).Name ' Right most sheet Debug.Print .Worksheets(.Worksheets.Count).Name End With End Sub Using the Code Name of a WorksheetThe best method of accessing the worksheet is using the code name. Each worksheet has a sheet name and a code name. The sheet name is the name that appears in the worksheet tab in Excel. Changing the sheet name does not change the code name meaning that referencing a sheet by the code name is a good idea. You can change both the sheet name and the code name in the property window of the sheet(see image below). ' https://excelmacromastery.com/ Public Sub UseCodeName2() ' Using the code name of the worksheet Debug.Print CodeName.Name CodeName.Range("A1") = 45 CodeName.Visible = True End Sub Code Name in other WorkbooksThere is one drawback to using the code name. It can only refer to worksheets in the workbook that contains the code i.e. ThisWorkbook. ' https://excelmacromastery.com/ Public Sub UseSheet() Dim sh As Worksheet ' Get the worksheet using the codename Set sh = SheetFromCodeName("CodeName", ThisWorkbook) ' Use the worksheet Debug.Print sh.Name End Sub ' This function gets the worksheet object from the Code Name Public Function SheetFromCodeName(Name As String, bk As Workbook) As Worksheet Dim sh As Worksheet For Each sh In bk.Worksheets If sh.CodeName = Name Then Set SheetFromCodeName = sh Exit For End If Next sh End Function There is another way of getting the sheet name of an external workbook using the code name. You can use the VBProject element of that Workbook. ' https://excelmacromastery.com/ Public Function SheetFromCodeName2(codeName As String _ , bk As Workbook) As Worksheet ' Get the sheet name from the CodeName using the VBProject Dim sheetName As String sheetName = bk.VBProject.VBComponents(codeName).Properties("Name") ' Use the sheet name to get the worksheet object Set SheetFromCodeName2 = bk.Worksheets(sheetName) End Function Code Name SummaryThe following is a quick summary of using the Code Name
The Active SheetThe ActiveSheet object refers to the worksheet that is currently active. You should only use ActiveSheet if you have a specific need to refer to the worksheet that is active. Otherwise you should specify the worksheet you are using. If you use a worksheet method like Range and don’t mention the worksheet, it will use the active worksheet by default. ' Write to Cell A1 in the active sheet ActiveSheet.Range("A1") = 99 ' Active sheet is the default if no sheet used Range("A1") = 99 Declaring a Worksheet ObjectDeclaring a worksheet object is useful for making your code neater and easier to read. The next example shows code for updating ranges of cells. The first Sub does not declare a worksheet object. The second sub declares a worksheet object and the code is therefore much clearer. ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub0 ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub1 ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub2 Accessing the Worksheet in a NutshellWith all the different ways to access a worksheet, you may be feeling overwhelmed or confused. So in this section, I am going to break it down into simple terms ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub3 ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub4 ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub5 ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub6 Add WorksheetThe examples in this section show you how to add a new worksheet to a workbook. If you do not supply any arguments to the Add function then the new worksheet will be placed before the active worksheet. When you add a Worksheet, it is created with a default name like “Sheet4”. If you want to change the name then you can easily do this using the Name property. ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub7 ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub8 Delete WorksheetTo delete a worksheet you simply call the Delete member. ' https://excelmacromastery.com/ Public Sub WriteToCell1() ' Worksheets refers to the worksheets in the active workbook Worksheets("Sheet1").Range("A1") = "Hello World" Worksheets("Sheet2").Range("A1") = "Hello World" Worksheets("Sheet3").Range("A1") = "Hello World" End Sub9 ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible0 If you try to access the worksheet after deleting it you will get the “Subscript out of Range” error we saw in the Accessing the Worksheet section. ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible1 Run-Time error -21147221080 (800401a8′) Automation Error If you are using the Code Name of the worksheet rather than a variable, then this will cause Excel to crash rather than give the automation error. ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible2 If you assign the Worksheet variable to a valid worksheet it will work fine ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible3 Loop Through the WorksheetsThe Worksheets member of Workbooks is a collection of worksheets belonging to a workbook. You can go through each sheet in the worksheets collection using a For Each Loop or a For Loop. ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible4 ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible5 Note: If you use code like this to write to worksheets then back everything up first as you could end up writing the incorrect data to all the sheets. ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible6 Using the Sheets CollectionThe workbook has another collection similar to Worksheets called Sheets. This causes confusion at times among users. To explain this first you need to know about a sheet type that is a chart.
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible7 ConclusionThis concludes the post on the VBA Worksheet. I hope you found it useful. The three most important elements of Excel VBA are Workbooks, Worksheets and Ranges and Cells. These elements will be used in almost everything you do. Understanding them will make you life much easier and make learning VBA much simpler. What’s Next?Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial. Related Training: Get full access to the Excel VBA training webinars and all the tutorials. (NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.) |