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.
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.
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:
In the screen shot below, the second sheet has been renamed as "TransData".
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:
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.
To change the sheet's code name:
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":
However, if the macro refered to the code name instead, it would continue to run without errors:
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.
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
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.
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
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
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.
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.
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
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.
This is the master sheet from the sample file, and it will be copied to create a new sheet each month
In the ThisWorkbook code module, there is a Workbook_Open event, and it runs the AddMonthWkst macro.
Private Sub Workbook_Open() AddMonthWkst End Sub
In 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
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:
The macro will replace those characters when creating the sheets.
In 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
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
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
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.
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
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.
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
Last updated: August 4, 2021 3:46 PM
Contextures RSS Feed