Contextures

Excel Worksheet Macros

Sample macros and VBA settings for working with Excel worksheets. Also see the Formula Auditing macro, for a quick way to spot formula setup problems on a worksheet.

Set a Worksheet Code Name

Each sheet in an Excel workbook has a visible name, and a hidden code name. Here's how you can see those names, and change them.

Sheet Name

The sheet's name appears on its sheet tab. By default, each new sheet is named "Sheet", followed by the next available number, such as "Sheet2".

You can change a sheet's name:

  • Double-click on the sheet tab, to select the existing name
  • Type a new name (up to 31 characters), using letters, numbers, spaces and underscores.
  • Press Enter, to complete the name

In the screen shot below, the second sheet has been renamed as "TransData".

worksheet name on sheet tab

Sheet Code Name

Each 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:

  • On the Ribbon, click the Developer tab (How to show the Developer tab)
  • In the Controls group, click Properties

worksheet name on sheet tab

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.

worksheet name on sheet tab

Change the Sheet's Code Name

To change the sheet's code name:

  • In the Properties window, double-click the code name, to select it
  • Type a new code name -- one word only -- with letters, numbers, and underscores. Keep the name short and meaningful.
  • Press Enter, to complete the code name change

worksheet name on sheet tab

Use Sheet Code Names in Macros

When 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 Sheets

If 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.

To 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.

warning grouped sheets

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

Change Workheet Appearance Settings

The following macros turn the workbook/worksheet appearance settings on or off. The first two macros affect only the active sheet. The next two macros affect all sheets in the active workbook.

To change these settings manually, click the File tab on the Ribbon, and click Options. Then click Advanced, and scroll down to the "Display options for this workbook" and "Display options for this worksheet" sections.

change worksheet appearance settings

Active Worksheet

The 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 Workbook

The 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

Worksheet Custom Footer Macro

There are built in header and footer options in Excel, but if you need something different, or if you want to pull information from named cells in the worksheet, you can use a macro, similar to the sample code shown below.

To see how to set up the named cells, and an explanation of how the code works, you can watch this short video. The written instructions and code are below the video.

Using the Custom Footer Macro

In 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.

change worksheet appearance settings

Custom Footer Macro Code

The 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.

change worksheet appearance settings

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

Add Month Sheet Automatically

Set 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.

Master Sheet

This is the master sheet from the sample file, and it will be copied to create a new sheet each month

change worksheet month sheets from master

ThisWorkbook Module

In the ThisWorkbook code module, there is a Workbook_Open event, and it runs the AddMonthWkst macro.

Private Sub Workbook_Open()
    AddMonthWkst
End Sub

modSheet Module

In the modSheets code module, there is macro named AddMonthWkst. This macro:

  • sets the sheet which will be used as the Master
  • calculates the current year and month name, in yyyy_mm format
  • checks for a sheet with that year/month name
  • if not found, it creates a new sheet, based on Wkst_Master
  • names new sheet with current year and month

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 Sheets

Set 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:

  • Use yy or yyyy to include year number
  • Use xx, xxx, or xxxx to include month name or number

The macro will replace those characters when creating the sheets.

change worksheet month sheets from master

modSheet Module

In the modSheets code module, there is macro named AddMultiMonthWksts. This macro:

  • calculates the selected year and month names
  • uses those in sheet name, and title (if entered)
  • checks for a sheet with that name
  • if not found, it creates a new sheet, based on Wkst_Master

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 Password

This 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 Password

This 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

Video: Protect Macro Fails When Sheets Grouped

To see the problem that occurs when you try to protect or unprotect group sheets, and the code change that fixes it, please watch this short Excel video tutorial.

Hide Specific Worksheets

Worksheets can be set to xlSheetVeryHidden, xlSheetHidden or xlSheetVisible

This macro hides all the worksheets in the active workbook, if the sheet name starts with "Admin". The sheets are set ot "xlSheetVeryHidden", so those sheet names don't appear if you right-click a sheet tab, and click Unhide.

To show them, run a macro that changes the setting to "xlSheetVisible", or go into the Visual Basic Editor, and manually change the worksheet's Visible property.

Also see: Hide Sheets Based on Tab Colour

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

Sheet Info List Macro

Use 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.

Copy the following code into your workbook, or get the code from the Sheet List sample file, below.

list of sheets in active workbook

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

  1. Month Sheets From Master: Download the sample workbook to automatically add month sheets, based on the Master sheet. The file is zipped, and is in xlsm format.
  2. Multiple Month Sheets From Master: Download the sample workbook to add multiple month sheets, based on the Master sheet. The file is zipped, and is in xlsm format.
  3. Sheet Info List Macro: Download the sample workbook to create a list of all sheets in active workbook. The file is zipped, and is in xlsm format.

More Tutorials

Worksheets, Freeze and Zoom Macros

Formula Auditing macro

List & Close Workbooks

Workbook Backup Tool, Free

PDF Format, Macros to Save Sheets As

Worksheet Tips

Hide Sheets Based on Tab Colour

Worksheet Protection Selector

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started

Last updated: August 4, 2021 3:46 PM
Contextures RSS Feed