Excel Macro to Inventory All of Your Tabs

by Matthew Kuo on February 7, 2016

in Error Checking, Excel, VBA

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

Auditing an Excel file with a ton of tabs is one of the least enjoyable experiences in Excel.  When I worked in consulting, it wasn’t uncommon for me to receive a file from a client containing 50 more spreadsheet tabs.  And as an analyst, I had the unfortunate task of having to review all of them.

While there are several different audits you can perform on an Excel file, one thing I like to do whenever I get a tab-heavy file is create an inventory of all of the tabs.  Doing so provides several benefits:

  • It gives me a sense of how the model is structured
  • It allows me to categorize the tabs by function: input, output, calculation, etc.
  • After categorizing them, I can usually tell which tabs are the most important
  • Also after categorizing them, I can usually cross off a number of unimportant tabs that I don’t need to review
  • It encourages me to account for tabs that might be hidden
  • It provides me a checklist, so I can track every tab I’ve reviewed

Not every Excel file you receive will warrant an audit at this level of detail, but it’s good to have this tab inventory option for when the situation calls for it.

Macro Purpose

The purpose of the following macro is to automate this tab inventory process

If you have an Excel file with only a few tabs, your best bet is just to create your inventory manually.  If, however, you have a file with huge number of tabs, let’s say 20 or more, you’re probably better off using a macro.  The process of copying the name of each tab, over a very large file, can get clunky and you’re much more likely to make a mistake.

The Macro

Below is the code for the macro that is needed to create an inventory of all of your tabs.  I don’t have a many posts about VBA on this blog, but to be safe, my assumption will be that you haven’t used VBA before and don’t have an interest in going through the details of the code.  Therefore, I’ll go through the process of implementing the code in detail.

Click here to download a workbook with the Excel Tab Inventory macro

Sub ExcelTabInventoryMacro()

Dim wksht As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
NewTabName = "Tab_Inventory"
Application.Sheets(NewTabName).Delete
Application.Sheets.Add Application.Sheets(1)
Set wksht = Application.ActiveSheet
wksht.Name = NewTabName
For i = 2 To Application.Sheets.Count
wksht.Range("A" & (i - 1)) = Application.Sheets(i).Name
Next
Application.DisplayAlerts = True
Sheets("Tab_Inventory").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Tab Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("A1").Select

End Sub

How to Implement the Macro

Please note that the example workbook is a macro enabled file.  Therefore, to either use the file or implement the code yourself, you’ll need to have macros enabled to be able to use it.

Click here to download a workbook with the Excel Tab Inventory macro

Step 1: Open the Visual Basic interface

The easiest way to do this is to hit ALT + F11.  If you have the “Developer” tab enabled, you can also go to that section of the ribbon and click on “Visual Basic”.

Excel Tab Inventory Macro 00

To enable the Developer tab, go to File Options Customize Ribbon

Then click the checkbox for “Developer”

Excel Tab Inventory Macro 01

Step 2: Within Visual Basic, click Insert  Module to create a new macro

Excel Tab Inventory Macro 02

Step 3: Paste the macro code above into your newly created module

Excel Tab Inventory Macro 03

Step 4: Hit the Play button to run your macro

You can also hit F5 to run your macro.

Excel Tab Inventory Macro 04

Step 5: Exit out of the Visual Basic interface

You can either close out of Visual Basic by hitting the “X” in the upper right, or just hit ALT + F11 again.

Excel Tab Inventory Macro 06

Once you return to Excel, you’ll see you have a new tab in your file titled “Tab_Inventory”, with the initial inventory headers in place for you.  (I only put in the titles of the headers, and none of the formatting I’d typically do, as I wanted to keep the code light)  From here you can use this baseline to build out your tab inventory.

Excel Tab Inventory Macro 07

Click here to download a workbook with the Excel Tab Inventory macro

{ 2 comments… read them below or add one }

July 25, 2016 at 9:33 pm

As an amateur VBA Excel programmer, I thought I could take a few minutes and rewrite your sub without using recorded code, to make it more efficient. The use of an array variable to prepare the list of sheet names, rather than entering the sheet names one by one directly to the new worksheet, will make a noticeable difference in reducing the time this sub will need to execute when lots of sheets exist, which is the point of this whole exercise. I tested it using a mix of worksheets and chart sheets. The comments are FYI, and not necessary to include. You may do with it what you will.
Best regards.

‘Indenting makes your code easier to read
Private Sub ExcelTabInventoryMacro()
‘Use a constant instead of a variable when the value won’t change
Const NewTabName As String = “Tab_Inventory”
Dim wksht As Worksheet
Dim intWSCnt As Integer
Dim i As Integer
‘List all sheet names in an array, then put the array in the worksheet; _
faster than entering each sheet name one at a time
Dim astrWSNames() As String
With Application
.DisplayAlerts = False
.ScreenUpdating = False ‘Reduces time for code to execute
End With
With ActiveWorkbook
On Error Resume Next
.Sheets(NewTabName).Delete
On Error GoTo 0 ‘Turn off error checking
‘Set up data for “Tab_Inventory” workhsheet before adding it
intWSCnt = .Sheets.Count
‘Need a 2-dimensional array to put into the worksheet; _
trying to use a 1-dimensional array will cause an error
ReDim astrWSNames(1 To intWSCnt, 1 To 1)
For i = 1 To intWSCnt
astrWSNames(i, 1) = .Sheets(i).Name
Next
Set wksht = .Worksheets.Add(Before:=.Worksheets(1))
End With
‘You don’t need to select a cell to put something in it
With wksht
.Name = NewTabName
.Range(“A1”).Value = “Tab Name”
.Range(“B1”).Value = “Type”
.Range(“C1”).Value = “Comments”
.Range(“A2”).Resize(intWSCnt) = astrWSNames()
.Columns(“A:A”).AutoFit ‘Widen the column so sheet names are completely visible
End With
‘Purge object variables from memory
Set wksht = Nothing
‘Don’t forget to restore Application settings!!
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Reply

Roman August 24, 2016 at 8:55 am

Thank you for this Macro, this is exactly what I need to check and consolidate messy files with a lot of tabs.

Reply

Leave a Comment

Previous post:

Next post:


Related pages


how do you delete rows in excelexcel vba create sheetwhat is hlookup in excel used forcreating a histogram in excel 2010match and vlookupucla student technology centermicrosoft excel lookuprandom student pickerexcel stacked bar graphexcel formula to remove duplicatescase interview zs associatescheap ucla gearhow to build formulas in excelhow to insert a total row in excellabour histogramfree primo pdf converterwhat do you mean by vlookup in excelmicrosoft excel learning guideargument excel definitiontypes of referencing in excelvlookup with two columns100 stacked column chartexcel chart data labelsmicrosoft excel duplicate finderbin range in histogramexcel converter to pdfucla bruinbusexcel calendar inputhistogram data analysis excelprofessional excel graphsexcel formula hlookupexcel if blank cellexcel two way lookuphow to find the duplicate values in excelif and vlookup togethercolumn chart excel 2010ucla anderson marketingconverting excel file to pdfhow to learn vlookup in excel 2007femba andersonwingdings checkmarkfind duplicates in a column in excelclass schedule excel templateif formulas in excel 2007how to create a bin in excelexcel dragging formulasexcel bar chart stackedexcel most useful formulasindexing formulashortcut keys for excelmax excel formulaifs formula excelfinancial modeling excel templatesbell curve calculator excelmicrosoft access 2010 free trialhow to graph multiple lines in exceltax estimator irsaxis title excel 2010vlookup with multiple conditionsprimo pdf software free downloadinventory template for exceliserror in excelto compare two columns in excelw-4 worksheet calculatorround to the nearest 1000 in excelfilter formula in excelexcel formula multiple ifexcel financial formulas cheat sheethow to transpose data in excel 2007advanced excel tips tricksstacked columns in excelbasic formulas of excelvlookup syntaxvlookup from another sheetexcel macro codes
\n