To export tab names in Excel, run a short VBA macro that copies every sheet name into a new workbook, then save that workbook as Excel or CSV. This is useful for creating an inventory, sharing a list of tabs, or documenting a workbook without copying the sheet contents. The steps below are for Excel for Microsoft 365 on Windows; use the desktop Excel app because Excel for the web cannot run VBA macros.
The macro preserves the order of the tabs and includes hidden worksheets and chart sheets. It creates a separate output workbook, so it does not change the source workbook. Save a backup first, and make sure you can run macros from a trusted file.
What You’ll Need
- Excel for Microsoft 365 on Windows, or desktop Excel for Mac with VBA support. Excel for the web can open the resulting file but cannot run this macro.
- The workbook whose tab names you want to export. Save it before you begin so the macro runs against the latest version.
- Permission to use VBA macros. Do not change your organization’s macro security settings just to run code from an unknown source.
- A destination and format in mind: keep the exported list in an .xlsx workbook for editing, or save it as a .csv file for use in other programs.
Step-by-Step
- Open the source workbook and save a backup. Open the Excel file that contains the tabs. Use File > Save As to make a backup copy before adding or running code. The macro described here writes to a new workbook, but a backup is still sensible protection when working with macros. If you intend to keep the macro inside the source file, save that file as an Excel Macro-Enabled Workbook (*.xlsm); an .xlsx file cannot store VBA code.
- Open the Visual Basic Editor. On Windows, press Alt+F11, or select Developer > Visual Basic. If the Developer tab is not visible, go to File > Options > Customize Ribbon, select the Developer check box under Main Tabs, and choose OK. On Mac, use Tools > Macro > Visual Basic Editor or the Developer tab if it is displayed. Depending on your Mac keyboard, you may need Fn with Option+F11.
- Add a standard module to the correct workbook. In the Visual Basic Editor, find the Project pane and select the project for the workbook whose tab names you want. Choose Insert > Module. If the Project pane is hidden, use View > Project Explorer. The macro must be stored in the source workbook for ThisWorkbook to refer to the right file; placing it in a different workbook, such as Personal.xlsb, can make it export the wrong set of tabs.
- Paste the macro into the module. Copy the code below into the large code window. It creates a new workbook, writes a heading and one tab name per row, and follows the tab order in the source file. It includes worksheets and chart sheets, including hidden ones.
Sub ExportTabNames()
Dim outputBook As Workbook
Dim outputSheet As Worksheet
Dim sourceSheet As Object
Dim tabIndex As Long
' Create a new workbook with one worksheet for the exported list.
Set outputBook = Workbooks.Add(xlWBATWorksheet)
Set outputSheet = outputBook.Worksheets(1)
outputSheet.Range("A1").Value = "Tab name"
' Copy each worksheet or chart sheet name in tab order.
tabIndex = 1
For Each sourceSheet In ThisWorkbook.Sheets
outputSheet.Cells(tabIndex + 1, 1).Value = sourceSheet.Name
tabIndex = tabIndex + 1
Next sourceSheet
outputSheet.Columns("A").AutoFit
outputSheet.Activate
MsgBox "Exported " & tabIndex - 1 & " tab names to a new workbook."
End Sub
In VBA code, the ampersands in the final message join text and a number; if you paste from this article, make sure the code contains the normal VBA character &, not the HTML text &. The actual line should read: MsgBox "Exported " & tabIndex - 1 & " tab names to a new workbook." If the editor flags that line, replace it with the following simpler version: MsgBox "Tab names exported to a new workbook."
- Run the macro. Click anywhere inside the procedure and press F5, then select ExportTabNames if Excel asks which macro to run. You can also close the editor and choose Developer > Macros, select ExportTabNames, and click Run. On Mac, use the Macros command on the Developer tab or Tools > Macro > Macros, then choose the procedure and run it.
- Check the new workbook. Excel should open a separate workbook with a single worksheet. Cell A1 contains “Tab name”; the names appear beneath it, one per row. Compare the first and last entries with the source workbook’s tab bar, and check that the number of entries looks reasonable. The source workbook remains open and unchanged.
- Save the exported list. To keep a normal Excel file, use File > Save As and save the new workbook as an .xlsx file. To make a CSV, use File > Save As, choose CSV UTF-8 (Comma delimited) (*.csv) where available, and save. Excel may warn that CSV keeps only the active sheet and removes workbook features. That is expected here because the export workbook contains just the list; choose the option to use the CSV format if that is what you want. Keep the .xlsx copy if you also want formatting or an editable workbook.
- Close the editor and source file safely. If you added the macro to the source workbook and want to use it again, save the source as .xlsm and keep that file. If you only needed a one-time export, you can close the source without saving the code after saving the separate output workbook. Do not save the source as .xlsx expecting it to retain the macro.
Common Mistakes
- Putting the macro in the wrong project: ThisWorkbook means the file that contains the code. If the code is in another workbook, the list may come from that other file. Confirm the selected project name before inserting the module.
- Expecting the list to update automatically: The macro makes a snapshot. If you rename, add, or delete tabs later, run it again to create an updated export.
- Forgetting hidden tabs: The macro includes hidden sheets and chart sheets, not just the visible worksheet tabs. This is intentional so the export is comprehensive. If you only want visible worksheets, the code needs to be adjusted.
- Saving the output in the wrong format: A CSV is plain text and does not preserve multiple sheets, formatting, or formulas. Use .xlsx if you want a more flexible list, and retain a separate copy if you need both formats.
- Confusing the new workbook with the source: The output opens as a separate workbook. Check the workbook title bar before saving so you do not overwrite the original file with the exported list.
Tips
Use the .xlsx output when you expect to sort, filter, annotate, or share the list with other Excel users. You can add columns beside the names for an owner, purpose, status, or review date without changing the source workbook. If another system requests a simple text file, CSV is usually more portable; choose UTF-8 when the option is available, especially if tab names contain accented letters or other non-English characters.
For a repeatable process, keep a macro-enabled copy of the workbook and give the macro a recognizable name. After adding or renaming sheets, run the macro again and save the newly generated export with a date or version in its filename. If your workbook has many tabs, use the exported list to search or sort names rather than trying to capture the tab bar in a screenshot. Remember that this method exports names, not sheet contents, tab colors, or visibility settings.
Troubleshooting
- The macro does not appear in the Macros list: Confirm that the code starts with
Sub ExportTabNames()and ends withEnd Sub, and that it is in a standard module rather than a worksheet or ThisWorkbook code window. Also check that you selected the source workbook’s project. - Excel blocks the macro: If the file is from an untrusted source, do not enable its macros blindly. For a workbook you trust and control, follow your organization’s approved process for opening macro-enabled files. Do not choose “Enable all macros” as a general security setting.
- The output contains names from the wrong workbook: The macro is using ThisWorkbook, which is the workbook containing the code. Move or copy the module into the workbook whose tabs you want to list, then run it there. Avoid changing it to ActiveWorkbook unless you understand that the active file can change while a macro runs.
- The exported list looks incomplete: This macro uses the Sheets collection, so it should include chart sheets and hidden sheets as well as visible worksheets. Check whether the source tabs were added after the macro ran; rerun the procedure to refresh the snapshot. If a name appears shortened in the cell, widen column A or inspect the full value in the formula bar.
- You are using Excel for the web: Open the workbook in the desktop Excel app to run VBA. You can still open and edit the exported .xlsx or .csv in Excel for the web after the desktop app creates it.
Frequently Asked Questions
Can Excel export worksheet names without VBA?
Excel does not provide a standard ribbon command that exports every tab name to a list. If macros are not allowed, you can copy names manually, but Excel does not offer a reliable built-in one-click export for all tabs. In managed workplaces, ask whether an approved macro or Office Script is available.
Does this include hidden sheets?
Yes. The macro loops through ThisWorkbook.Sheets, which includes hidden worksheets and chart sheets. It exports their names but does not change their visibility or reveal them.
Can I export the names to a CSV file?
Yes. Run the macro, then save the new one-sheet workbook as CSV UTF-8 (Comma delimited) (*.csv), if that format is offered. CSV stores the list as plain text and will not preserve Excel formatting or workbook features.
Will the exported list change when I rename a tab?
No. It is a point-in-time copy, not a live connection. Run the macro again after renaming, adding, or removing tabs to make a refreshed list.
Can I run the macro on a Mac or in Excel for the web?
Desktop Excel for Mac supports VBA, though its editor and keyboard shortcuts can differ from Windows. Excel for the web does not run VBA macros, so use desktop Excel to create the export and then open the result online if needed.
Related
- How to Edit Custom Number Formats in Excel Step by Step
- How to Edit Border Color in Excel: Step-by-Step Guide
- How to Duplicate a Text Box in Excel Without Rebuilding It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table Creation: How to Set Up and Use TablesTo create an Excel table in Microsoft 365 for Windows, select a cell in a contiguous data range,…
- Percentage Difference Formula in Google SheetsIn Google Sheets, percentage difference is the absolute difference between two values divided by their average. If A2…
- How to Add or Remove a Watermark in Microsoft WordIn Word for Microsoft 365 on Windows, open the document and choose Design > Watermark to add a…
- How to Hyperlink to Another Cell in ExcelTo hyperlink to another cell in the same Excel workbook, select the cell where the link should appear,…
- Excel Data Validation: Lists, Rules, and SetupExcel data validation controls what people can enter in selected cells. In Microsoft Excel for Microsoft 365 on…
- Excel Keyboard Shortcuts Cheat Sheet for Windows UsersFor Excel for Microsoft 365 on Windows, the most useful shortcuts include Ctrl+C to copy, Ctrl+V to paste,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.