Home > Macros > Basics > Worksheets Excel Worksheet MacrosSample macros and VBA settings for working with Excel worksheets and grouped sheets. Also see the Formula Auditing macro, for a quick way to spot formula setup problems on a worksheet. |
Sheet Code NameEach sheet also has a hidden code name. By default, each new sheet is code-named "Sheet", followed by the next available number, such as "Sheet2". NOTE: A sheet's default code name might not be the same as the sheet's default name. To see the active sheet's code name:
In the Properties window, the (Name) property, at the top, is the code name. The Name property (without brackets), is the name that appears on the sheet tab. In the screen shot below, the code name is Sheet2, and the name is TransData. |
Change the Sheet's Code NameTo change the sheet's code name:
|
Use Sheet Code Names in MacrosWhen you create macros that refer to specific worksheets, it is better to use the code names, than the sheet tab names, because the code names are unlikely to be changed by the users. For example, this code would result in an error, if the sheet name was changed from "TransData" to "Transactions": Sheets("TransData").Activate However, if the macro refered to the code name instead, it would continue to run without errors: wsTD.Activate Count the Selected SheetsIf you select more than one sheet in a workbook, the sheets are grouped, and that can cause problems. For example, if you manually enter data while sheets are grouped, that data will be entered in all the selected sheets, not just the active sheet. Or, if you try to run code that protects or unprotects all the sheets, the code will fail if the sheets are grouped, as you can see in the video below. To prevent these problems, you can count the number of sheets that are selected. If the count is higher than 1, show a warning message, so you will remember to ungroup the sheets. |
Macro to Check for Grouped SheetsTo use this code, add it to a regular code module in your Excel file. Then, you could run the code at the start of other macros, or add it to the Workbook_SheetActivate event code. Sub GroupedCount() Dim wdw As Window Dim ws As Worksheet On Error Resume Next Set wdw = ActiveWindow Dim Count As Long For Each ws In wdw.SelectedSheets Count = Count + 1 Next ws If Count > 1 Then MsgBox "WARNING! " & Count & " sheets are grouped", _ vbOKOnly + vbCritical, "Grouped Sheets" End If End Sub |
Macro: Change All Grouped SheetsWhen worksheets are grouped:
How can you do the same things with an Excel VBA macro? Here is the key rule:
In the sections below, I show an example.
1) Record Grouped Sheets MacroThe easiest way to create a macro in Excel is to record the steps, while you do a task manually. To get started with a macro to change all grouped sheets, follow these steps:
2) View the Recorded CodeNext, press Alt+F11, to open the Visual Basic Editor, and take a look at the recorded code. You'll see that the code has steps where sheets, or cells, or columns are selected, similar to the code shown below. Sub GroupedDeleteCol01() Sheets("Sheet2").Select Range("A1").Select Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Select Sheets("Sheet2").Activate Columns("C:C").Select Selection.Delete Shift:=xlToLeft Sheets("Sheet1").Select Range("A2").Select End Sub 3) Clean Up Recorded Code - PROBLEMNext, make a copy of the recorded code, and give the copy a different macro name Usually, I clean up recorded code, add comments, and remove any unnecessary steps. For example, in the revised macro below, I made these changes:
Sub GroupedDeleteCol02() 'group the sheets Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Select 'delete column C for grouped sheets Columns("C:C").Delete Shift:=xlToLeft 'ungroup the sheets Sheets("Sheet1").Select End Sub But, when I ran the code with those changes, it only deleted the column on the active sheet. Nothing changed on the other grouped sheets. 4) Clean Up Recorded Code - SUCCESSSo, it's okay to clean up the recorded code, but here's the important note about actions on grouped sheets:
In the revised macro below:
This code ran correctly, and deleted column C on all grouped sheets. Sub GroupedDeleteCol03() 'group the sheets Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Select 'select and delete column C in grouped sheets Columns("C:C").Select Selection.Delete Shift:=xlToLeft 'ungroup the sheets Sheets("Sheet1").Select End Sub |
Active WorksheetThe following macro turns ON gridlines, headings, page breaks and zeros for the active worksheet. The bSet variable is True, so all the settings are turned on. Sub SettingsActiveSheetOn() On Error Resume Next Dim bSet As Boolean bSet = True 'turns on headings, gridlines ' page breaks and zeros With ActiveWindow .DisplayHeadings = bSet .DisplayGridlines = bSet .DisplayPageBreaks = bSet .DisplayZeros = bSet End With End Sub |
The following macro turns OFF gridlines, headings, page breaks and zeros for the active worksheet. The bSet variable has been change to False, so all the settings are turned off Sub SettingsActiveSheetOff() On Error Resume Next Dim bSet As Boolean bSet = False 'turns on headings, gridlines ' page breaks and zeros With ActiveWindow .DisplayHeadings = bSet .DisplayGridlines = bSet .DisplayPageBreaks = bSet .DisplayZeros = bSet End With End Sub |
All Worksheets in Active WorkbookThe following macro turns ON scrollbars and sheet tabs for the active workbook -- it affects all the sheets. The bSet variable is True, so all the settings are turned on. Sub SettingsActiveWorkbookOn() On Error Resume Next Dim bSet As Boolean bSet = True 'turns on scrollbars and sheet tabs With ActiveWindow .DisplayVerticalScrollBar = bSet .DisplayHorizontalScrollBar = bSet .DisplayWorkbookTabs = bSet End With End Sub The following macro turns OFF scrollbars and sheet tabs for the active workbook -- it affects all the sheets. The bSet variable has been change to False, so all the settings are turned off Sub SettingsActiveWorkbookOff() On Error Resume Next Dim bSet As Boolean bSet = False 'turns on scrollbars and sheet tabs With ActiveWindow .DisplayVerticalScrollBar = bSet .DisplayHorizontalScrollBar = bSet .DisplayWorkbookTabs = bSet End With End Sub Hide or Show Gridlines All SheetsThe following two macros hide worksheet gridlines, or show worksheet gridlines, for all sheets in the active workbook. In both macros, the following steps are done:
Hide Gridlines All SheetsThe following macro turns OFF the worksheet gridlines, for all sheets in the active workbook. Sub HideGridAllSheets() Dim wsA As Worksheet Set wsA = ActiveSheet ActiveWorkbook.Worksheets.Select ActiveWindow.DisplayGridlines = False wsA.Select End Sub Show Gridlines All SheetsThe following macro turns ON the worksheet gridlines, for all sheets in the active workbook. Sub ShowGridAllSheets() Dim wsA As Worksheet Set wsA = ActiveSheet ActiveWorkbook.Worksheets.Select ActiveWindow.DisplayGridlines = True wsA.Select End Sub |
Using the Custom Footer MacroIn this example, there are named cells that contain the order date, customer name and order number. That data will be added to the footer, and the date will be formatted. The video below shows how to set this up. To test the macro, you can download the Excel Footer Macro sample workbook. The zipped file is in xlsm format. After you unzip the file and open it, enable macros, so you can run the code. |
Add Month Sheet AutomaticallySet up a Master sheet in your workbook, and automatically create a copy of that sheet each month, named for the month and year, in yyyy_mm format. Note: To add multiple month sheets, go to the next section: Add Multiple Month Sheets This macro runs automatically when the workbook opens, and macros are enabled. There are two bits of code in the sample workbook. There is a sample file with these macros in the Download section below. Watch this video to see the steps, and the code and instructions are below the video. |
Custom Footer Macro CodeThe following code adds the Order Date, Customer Name, and Order Number to the footer, based on the contents fo the named cells on the order sheet. The date formatted as dd-mmm-yyyy, and you could change to a different date format, such as "yyyy-mm-dd". You can manually run the code, after entering the order information in the named cells. Or, you could add it to the Workbook module, in the BeforePrint event. Sub SetFooter() ' put customer name, date and Order Number in footer ' www.contextures.com Dim wb As Workbook Dim wsOrder As Worksheet Dim strCust As String Dim strOrder As String Dim dtmDate As Date Set wb = ThisWorkbook Set wsOrder = wb.Sheets("Orders") dtmDate = wsOrder.Range("OrderDate").Value strCust = wsOrder.Range("CustName").Value strOrder = wsOrder.Range("OrderNum").Value With wsOrder.PageSetup .LeftFooter = _ "Customer Name: " & strCust & Chr(10) & _ "Order: " & strOrder .CenterFooter = "" .RightFooter = _ "Date: " & Format(dtmDate, "dd-mmm-yyyy") End With End Sub |
Master SheetThis is the master sheet from the sample file, and it will be copied to create a new sheet each month ThisWorkbook ModuleIn the ThisWorkbook code module, there is a Workbook_Open event, and it runs the AddMonthWkst macro. Private Sub Workbook_Open() AddMonthWkst End Sub modSheet ModuleIn the modSheets code module, there is macro named AddMonthWkst. This macro:
NOTE: You can change the master sheet name, and the date formatting, to match what you need in your workbook Sub AddMonthWkst() Dim ws As Worksheet Dim wsM As Worksheet Dim strName As String Dim bCheck As Boolean On Error Resume Next Set wsM = Sheets("Wkst_Master") strName = Format(Date, "yyyy_mm") bCheck = Len(Sheets(strName).Name) > 0 If bCheck = False Then 'add new sheet after Instructions wsM.Copy After:=Sheets(1) ActiveSheet.Name = strName End If Set wsM = Nothing End Sub |
Add Multiple Month SheetsSet up a Master sheet, named Wkst_Master, in your workbook, and use this macro to automatically create copies of that sheet, for multiple months. Get the sample file in the Download section below. To customize the sheets, go to the sheet named Wkst_Admin, and enter the year, month, sheet name and sheet title information. For the sheet names and sheet titles:
The macro will replace those characters when creating the sheets. |
modSheet ModuleIn the modSheets code module, there is macro named AddMultiMonthWksts. This macro:
NOTE: If you change the Admin sheet or Master sheet names, also change those names in the code. Sub AddMultiMonthWksts() Dim wsA As Worksheet Dim wsM As Worksheet Dim wsMth As Worksheet Dim bCheck As Boolean Dim wsYr As Long Dim strCell As String Dim strTitle As String Dim strTabs As String Dim wsTitle As String Dim wsTab As String Dim wsStart As Long Dim wsEnd As Long Dim wsSht As Long Dim shDate As Date Dim strMthT As String Dim strMthTb As String Dim strYtT As String Dim strYrTb As String Dim strMsg As String On Error Resume Next Set wsA = Sheets("Wkst_Admin") Set wsM = Sheets("Wkst_Master") strMsg = "Could not add all sheets" On Error Resume Next With wsA wsYr = .Range("wsYr").Value If wsYr = 0 Then strMsg = "Please enter year number" _ & vbCrLf _ & "and try again." GoTo errHandler End If strCell = .Range("wsCell").Value strTitle = .Range("wsTitle").Value strTabs = .Range("wsTabs").Value wsStart = .Range("wsStart").Value wsEnd = .Range("wsEnd").Value If wsStart = 0 Or wsEnd = 0 Then strMsg = "Please enter month start " _ & "and end numbers" _ & vbCrLf _ & "and try again." GoTo errHandler End If End With On Error GoTo errHandler 'add sheets before master sheet For wsSht = wsStart To wsEnd strMsg = "Could not get date for" _ & vbCrLf _ & "Year " & wsYr _ & " and Month " & wsSht shDate = DateSerial(wsYr, wsSht, 1) strMsg = "Could not get sheet name for" _ & vbCrLf _ & "Year " & wsYr _ & " and Month " & wsSht wsTab = Range("wsTabs").Value wsTab = Replace(wsTab, "xxxx", _ Format(shDate, "mmmm")) wsTab = Replace(wsTab, "xxx", _ Format(shDate, "mmm")) wsTab = Replace(wsTab, "xx", _ Format(shDate, "mmm")) wsTab = Replace(wsTab, "yyyy", _ Format(shDate, "yyyy")) wsTab = Replace(wsTab, "yy", _ Format(shDate, "yy")) On Error Resume Next bCheck = Len(Sheets(wsTab).Name) > 0 On Error GoTo errHandler If bCheck = False Then 'add new sheet after Instructions strMsg = "Could not get add sheet " & wsTab wsM.Copy Before:=wsM With ActiveSheet .Name = wsTab If strCell <> "" Then wsTitle = strTitle wsTitle = Replace(wsTitle, "xxxx", _ Format(shDate, "mmmm")) wsTitle = Replace(wsTitle, "xxx", _ Format(shDate, "mmm")) wsTitle = Replace(wsTitle, "xx", _ Format(shDate, "mm")) wsTitle = Replace(wsTitle, "yyyy", _ Format(shDate, "yyyy")) wsTitle = Replace(wsTitle, "yy", _ Format(shDate, "yy")) .Range(strCell).Value = wsTitle End If End With End If Next wsSht exitHandler: Set wsA = Nothing Set wsM = Nothing Set wsMth = Nothing Exit Sub errHandler: MsgBox strMsg Resume exitHandler End Sub |
Protect All Sheets No PasswordThis macro protects all the worksheets in the active workbook, with no password. Watch the video below, to see how it works. NOTE: If sheets are grouped, the ws.Protect code will not work. To avoid that problem, there is a line at the start to fhe macro, to select the first sheet in the workbook. You could do something fancier, like identifying the active sheet, and selecting it, instead of the first sheet. Sub ProtectAllSheetsNoPwd() Dim ws As Worksheet On Error Resume Next 'select first sheet, in case sheets are grouped Sheets(1).Select For Each ws In ActiveWorkbook.Worksheets ws.Protect DrawingObjects:=True, _ Contents:=True, Password:="" Next ws End Sub |
Unprotect All Sheets No PasswordThis macro unprotects all the worksheets, with no password. There is a line of code in the macro, to ungroup the sheets, if necessary. Watch the video below, to see how it works. NOTE: If sheets are grouped, the ws.Protect code will not work. To avoid that problem, there is a line at the start to fhe macro, to select the active sheet. Sub UnProtectAllSheetsNoPwd() Dim ws As Worksheet On Error Resume Next 'select active sheet, in case sheets are grouped ActiveSheet.Select For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Next ws End Sub |
Macro to Hide Admin WorksheetsThis macro hides all the worksheets in the active workbook, if the sheet name starts with "Admin". The sheets are set to a visibility of "xlSheetVeryHidden", so their sheet names don't appear if you right-click a sheet tab, and click Unhide. At the end of the macro, the first sheet is activated Sub HideAdminSheets() Dim ws As Worksheet On Error Resume Next For Each ws In ThisWorkbook.Worksheets If Left(ws.Name, 5) = "Admin" Then ws.Visible = xlSheetVeryHidden Else ws.Visible = xlSheetVisible End If Next ws Worksheets(1).Activate End Sub |
Macro to Show All SheetsTo show all the worksheets in a workbook again, run the following macro. This macro changes the visibility setting to "xlSheetVisible", so its sheet tab is visible. At the end of the macro, the first sheet is activated Sub ShowAdminSheets() Dim ws As Worksheet On Error Resume Next For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws Worksheets(1).Activate End Sub |
Set a Macro Keyboard ShortcutTo make it even easier to run the Admin sheet macros, you can create a keyboard shortcut for each macro.
In the Hide Specific Sheets video, the following macro shortcuts were created:
Run a Macro with Keyboard ShortcutAfter you set a macro keyboard shortcut, just press those shortcut keys, to instantly run the macro. For example, press Ctrl + Shift + H and the HideAdminSheets macro runs, changing all the Admin sheets to Very Hidden visibility setting. |
Sheet Info List MacroUse this macro to create a list of all the sheets in the active workbook, with details on the sheet layout and contents. The macro adds a new sheet to the workbook, with the sheet details in a named Excel table. There are hyperlinks to the sheets, and to the last cell in each sheet. Macro Code for Sheet Info ListCopy the following code into your workbook, or get the code from the Sheet List sample file, below. Sub ListSheetsInfo() 'contextures.com Dim ws As Worksheet Dim lCount As Long Dim wsTemp As Worksheet Dim rngF As Range Dim sh As Shape Dim lFields As Long Dim lTab As Long Dim lCh As Long Dim lSh As Long Dim rngDV As Range Dim rngCF As Range Dim vFml As Variant Dim vDV As Variant Dim vCF As Variant Dim vTbl As Variant Dim vSh As Variant Dim vCh As Variant Dim vPT As Variant Dim colLC As Long Dim strNA As String Dim strLC As String Application.EnableEvents = False Application.ScreenUpdating = False On Error Resume Next Set wsTemp = Worksheets.Add(Before:=Sheets(1)) lCount = 2 lFields = 14 'don't count tab color strNA = " --" colLC = 7 'column with last cell link With wsTemp .Range(.Cells(1, 1), _ .Cells(1, lFields + 1)).Value _ = Array( _ "Order", _ "Sheet Name", _ "Code Name", _ "Protected", _ "Used Range", _ "Range Cells", _ "Last Cell", _ "DV Cells", _ "CF Cells", _ "Tables", _ "Formulas", _ "Pivot Tables", _ "Shapes", _ "Charts", _ "Tab Color") End With For Each ws In ActiveWorkbook.Worksheets 'Do not list Very Hidden sheets ( If ws.Name <> wsTemp.Name _ And ws.Visible <> 2 Then strLC = ws.Cells _ .SpecialCells(xlCellTypeLastCell) _ .Address If ws.ProtectContents = True Then vTbl = strNA vSh = strNA vCh = strNA vPT = strNA vFml = strNA vDV = strNA vCF = strNA Else vTbl = ws.ListObjects.Count vPT = ws.PivotTables.Count vSh = ws.Shapes.Count vCh = ws.ChartObjects.Count vSh = vSh - vCh Set rngF = Nothing vFml = 0 Set rngF = ws.Cells _ .SpecialCells(xlCellTypeFormulas, 23) If Not rngF Is Nothing Then vFml = rngF.Cells.Count End If Set rngDV = Nothing vDV = 0 Set rngDV = ws.Cells _ .SpecialCells(xlCellTypeAllValidation) If Not rngDV Is Nothing Then vDV = rngDV.Cells.Count End If Set rngCF = Nothing vCF = 0 'xlCellTypeAllFormatConditions Set rngCF = ws.Cells.SpecialCells(-4172) If Not rngCF Is Nothing Then vCF = rngCF.Cells.Count End If End If With wsTemp .Range(.Cells(lCount, 1), _ .Cells(lCount, lFields)).Value _ = Array( _ ws.Index, _ ws.Name, _ ws.CodeName, _ ws.ProtectContents, _ ws.UsedRange.Address, _ ws.UsedRange.Cells.Count, _ strLC, _ vDV, _ vCF, _ vTbl, _ vFml, _ vPT, _ vSh, _ vCh, _ vPT) lTab = 0 lTab = ws.Tab.Color If lTab > 0 Then On Error Resume Next With .Cells(lCount, lFields + 1) ' .Value = lTab .Interior.Color = lTab End With End If 'add hyperlink to sheet name in col B .Hyperlinks.Add _ Anchor:=.Cells(lCount, 2), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ ScreenTip:=ws.Name, _ TextToDisplay:=ws.Name 'add hyperlink to last cell .Hyperlinks.Add _ Anchor:=.Cells(lCount, colLC), _ Address:="", _ SubAddress:="'" & ws.Name _ & "'!" & strLC, _ ScreenTip:=strLC, _ TextToDisplay:=strLC lCount = lCount + 1 End With End If Next ws With wsTemp .ListObjects.Add(xlSrcRange, _ .Range("A1").CurrentRegion, , xlYes) _ .Name = "" .ListObjects(1) _ .ShowTableStyleRowStripes = False End With Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Get the Sample Files
|
Worksheets, Freeze and Zoom Macros
PDF Format, Macros to Save Sheets As
Hide Sheets Based on Tab Colour
Last updated: March 2, 2023 11:11 AM