Forum Discussion

Jude_ward1812's avatar
Jude_ward1812
Copper Contributor
Feb 26, 2021

Worksheet names

Hi 

IS there a way of copying or exporting a list of the worksheet names so they can be copied and pasted into an overview worksheet at the start of the workbook

excel

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 27, 2021

    Jude_ward1812 

    To combine above two ideas

    1) We use =CELL("filename") as named cell within file

    2) We generate list of sheets by Power Query

     

    But there are even more limitations:

    - that's desktop version of Excel

    - this is the file kept on SharePoint site / OneDrive or on synced folder

     

    Script is

    let
    SourcePath = Excel.CurrentWorkbook(){[Name="filepath"]}[Content],
    #"Remove Sheet Name" = Table.SplitColumn(
        SourcePath, "Column1",
        Splitter.SplitTextByEachDelimiter({"]"}, QuoteStyle.Csv, true),
        {"path"}
    ),
    filepath = Table.ReplaceValue(
        #"Remove Sheet Name", "[","",
        Replacer.ReplaceText,{"path"})
        [path]{0},

    Source = Excel.Workbook(Web.Contents(filepath), null, true),
    #"Keep Sheets" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    #"Only Names" = Table.SelectColumns(#"Keep Sheets",{"Name"})
in
    #"Only Names"
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 27, 2021

    Jude_ward1812 Alternatively, use Power Query to list the workbook contents, applying the following steps in the Advanced editor.

    let
    Source = Excel.Workbook(File.Contents("FilePath\FileName.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"})
in
    #"Removed Other Columns"

     Replace "FilePath\FileName.xlsx" with the real file path and name of your file.

     

    Step by step:

    Save your file;

    Connect to the workbook itself (Get Data, From File, From Workbook);

    Navigate to the file (i.e. the file you just saved = the one you are still working in);

    Select any of the sheets (doesn't matter which one);

    Transform;

    The PQ editor opens with two applied steps (Source and Navigation). Delete the navigation step;

    You are left with an overview of everything in your file (Sheets, Tables, Named Ranges etc.);

    Filter all rows where Kind equals "Sheet";

    Keep only the "Name" column;

    Close & Load to a table in a new sheet to create a list of all sheet names in the workbook.

     

    When you add more sheets, first save the file and refresh the query. The new sheets will be added to your list automatically.

    • mtarler's avatar
      mtarler
      Silver Contributor
      Feb 26, 2021
      that technique works in desktop version but will require the workbook to be saved in a macro enabled version (.xlsm). If you just need it 1 time and don't want to save it as a macro sheet then you can ignore the warning and get the list of sheets.
      If you want this to be dynamic (i.e. have a list that updates as people add/remove sheets especially if they are copying a template sheet) then you can add a formula on each sheet that will regurgitate its own name (note the must be saved locally):
      =LET(path,CELL("filename",A1),RIGHT(path,LEN(path)-SEARCH("]",path)))
      or if you don't have LET()
      =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))
      and then use the following formula to create the list of sheets (you will need to input the first and last sheet in the range or you can use indirect to build that range name using 2 cells on the sheet where you enter those 2 sheet names:
      =TRIM(MID(TEXTJOIN(REPT(" ",100),,Sheet1:Sheet5!A1),SEQUENCE(SHEETS(),,1,100),100))
      and if you don't have dynamic arrays (and assuming you start on row 1 and drag down):
      =INDEX(TRIM(MID(TEXTJOIN(REPT(" ",100),,Sheet1:Sheet5!$A$1),SEQUENCE(SHEETS(),,1,100),100)),ROW())

Resources