Home > Macros > Basics > Worksheets

Excel Worksheet Macros

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

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

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

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

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.

Macro to Check for Grouped 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.

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 Sheets

When worksheets are grouped:

• If you manually enter data on one sheet, the same data is entered on all of the selected worksheets.
• If you delete a column on one of the grouped sheets, the same column is deleted in all the selected sheets.

How can you do the same things with an Excel VBA macro?

Here is the key rule:

• Use the Select method, before any action that should affect all of the grouped sheets

In the sections below, I show an example.

• First, I created code with the macro recorder, and then I cleaned it up.
• My first revision didn't work - only the active sheet was changed, not the other grouped sheets.
• In my second revision, I left in an important Select step, and the code ran correctly

1) Record Grouped Sheets Macro

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

• First, make a copy of your workbook, to use for testing.
• Next, in the workbook copy, turn on the macro recorder
• Enter a name for the macro, and store it in "This workbook"
• Then, select two or more worksheets, to group them
• On the active sheet, select column C, and delete it.
• Next, turn off the recorder
• Finally, press Ctrl+Z, to undo the column deletion.

2) View the Recorded Code

Next, 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 - PROBLEM

Next, 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:

• removed lines where a range is selected, for no reason
• delete column C, without selecting it

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 - SUCCESS

So, it's okay to clean up the recorded code, but here's the important note about actions on grouped sheets:

• DO NOT remove the "Select" steps, for actions that should affect all of the grouped sheets.

In the revised macro below:

• I removed unnecessary steps
• I DID NOT change the lines to select column C, and then delete the selection.

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

Change Worksheet 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.

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

Hide or Show Gridlines All Sheets

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

1. variable is set for the active sheet
2. all sheets are selected
3. gridline setting is changed
4. active sheet is selected again

Hide Gridlines All Sheets

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

The 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

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.

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.

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.

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 Sheet

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

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.

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

In an Excel workbook, you can manually hide or show worksheets, and you can apply worksheet visibility settings with a macro. Excel worksheets can have any one of the following visibility settings:

• VeryHidden - not visible in sheet tabs, not in the "Unhide Sheets" list
• Hidden- not visible in sheet tabs, appears in the "Unhide Sheets" list
• Visible - visible in sheet tabs

The video below shows a worksheet visibility macro that changes sheets in a workbook to "Very Hidden", if the sheet name starts with "Admin_". The video also shows how to set a macro keyboard shortcut, so it's easy to run the macro quickly, when you need to.

If you want to use the "Hide Specific Sheets" macro in your own workbooks, you can copy the VBA code that is below the video.

Macro to Hide Admin Worksheets

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

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

To make it even easier to run the Admin sheet macros, you can create a keyboard shortcut for each macro.

• Click the Developer tab on the Ribbon, then click Macros.
• Click on a macro name to select it, and click Options
• In the Shortcut Keys section, type a letter (upper or lower case) in the box.
  • For example, press the Shift key, while typing the letter H

In the Hide Specific Sheets video, the following macro shortcuts were created:

• Ctrl + Shift + H --run the HideAdminSheets macro
• Ctrl + Shift + A --run the ShowAdminSheets macro

Run a Macro with Keyboard Shortcut

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

Macro Code for Sheet Info List

Copy 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

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.