I am working with 40+ workbooks and need a list of every single named tab in each one, for a cataloguing project. The only macros and instructions I can find would require me to create a formula or macro for EACH workbook. I have one file I've designated as the macro file. In it are 2 functions already, one to sort tabs by color and one to sort them alphabetically, and both work on whatever workbook I have active, as long as the macro workbook is open in the background. Can someone please direct me to a macro that will list all the sheet names in the active workbook, that operates from the macro workbook? Thanks. (This will be a MASSIVE timesaver for me if someone can. Between all the workbooks, there are over 2100 tabs.) A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. I tested it, and... nothing happened. It works inside a workbook, I replaced "ThisWorkbook" with ActiveWorkbook and it should work for you. Try again... Sub ListSheetNames_R1() Dim Ndx As Long, sh As Object Ndx = 2 With ActiveWorkbook.Worksheets(1) .Cells(Ndx, 1).Resize(ThisWorkbook.Sheets.Count * 2, 1).ClearContents For Each sh In ActiveWorkbook.Sheets .Cells(Ndx, 1).Value = sh.Name Ndx = Ndx + 2 Next 'sh End With End Sub Nothing Left to Lose 8 additional answers Anonymous 2022-05-23T00:48:46+00:00 I tested it, and... nothing happened. 0 comments No comments I tested it, and... nothing happened. Anonymous 2022-05-23T00:39:16+00:00 Re: "direct me to a macro that will list all the sheet names in the active workbook" I posted code today in this forum to do just that. " Excel - List Sheet Names Coming up as Blocked " It starts the list in A2 and skips every other row... '--- Sub ListSheetNames() Dim Ndx As Long, sh As Object Ndx = 2 With ThisWorkbook.Worksheets(1) .Cells(Ndx, 1).Resize(ThisWorkbook.Sheets.Count * 2, 1).ClearContents For Each sh In ThisWorkbook.Sheets .Cells(Ndx, 1).Value = sh.Name Ndx = Ndx + 2 Next 'sh End With End Sub '--- To list the names in consecutive cells... Change: Ndx = Ndx + 2 To: Ndx = Ndx + 1 Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU ( free excel programs ) 0 comments No comments Re: "direct me to a macro that will list all the sheet names in the active workbook" I posted code today in this forum to do just that. " Excel - List Sheet Names Coming up as Blocked "

It starts the list in A2 and skips every other row... Sub ListSheetNames() Dim Ndx As Long, sh As Object Ndx = 2 With ThisWorkbook.Worksheets(1) .Cells(Ndx, 1).Resize(ThisWorkbook.Sheets.Count * 2, 1).ClearContents For Each sh In ThisWorkbook.Sheets .Cells(Ndx, 1).Value = sh.Name Ndx = Ndx + 2 Next 'sh End With End Sub To list the names in consecutive cells... Change: Ndx = Ndx + 2 To: Ndx = Ndx + 1 Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU ( free excel programs ) Anonymous 2022-05-23T00:19:04+00:00 They are already in one folder. I don't mind running the function in each one individually, jsyk, I just don't want to create macros in every single one. I'll check the VBA forum as well, thanks, I got the other two mentioned from here, which is why I started here. :) 0 comments No comments They are already in one folder. I don't mind running the function in each one individually, jsyk, I just don't want to create macros in every single one. I'll check the VBA forum as well, thanks, I got the other two mentioned from here, which is why I started here. :) Anonymous 2022-05-23T00:09:43+00:00 Hello Cygnata, Per the description shared, I understand you want to get the list of all Excel worksheet names in multiple Excel workbooks into one Excel workbook, and based on my research, we can meet your requirement by using Power Query but you need to place all your Excel workbooks into one folder and perform the Power Query from Folder option. For more reference, please look at this article: How To Get All Sheet Names From All Workbooks In A Folder Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology. In my further research about VBA, I didn't find the exact Macro to meet your requirement but in order to achieve it, we need to place all the Excel workbooks into the one specified folder. If you are looking for VBA to meet your requirement, you can post your concern in the related community to get detailed help from experts. Reference: Office VBA support and feedback | Microsoft Docs In addition, I will keep this thread open so Excel experts and MVPs can share their ideas regarding your requirement. Appreciate your understanding and patience!! Best Regards, Mia 0 comments No comments Per the description shared, I understand you want to get the list of all Excel worksheet names in multiple Excel workbooks into one Excel workbook, and based on my research, we can meet your requirement by using Power Query but you need to place all your Excel workbooks into one folder and perform the Power Query from Folder option. For more reference, please look at this article: How To Get All Sheet Names From All Workbooks In A Folder Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology. In my further research about VBA, I didn't find the exact Macro to meet your requirement but in order to achieve it, we need to place all the Excel workbooks into the one specified folder. If you are looking for VBA to meet your requirement, you can post your concern in the related community to get detailed help from experts. Reference: Office VBA support and feedback | Microsoft Docs In addition, I will keep this thread open so Excel experts and MVPs can share their ideas regarding your requirement. Appreciate your understanding and patience!!