List of Worksheet Names 1

[clifftech]

I need to list in Workbook1 all the worksheet names in Workbook2 (both Workbooks are open). I have tried using the cell() function but am not having any success.

Is this possible?

 

[SkipVought]

Hi,

How have you been using the CELL Function without success?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

I tried =cell("workbook2.xls",Sheet1!A1)

 

[AnotherHiggins]

Have a look at Excel's help file for "Cell Function" to see what the arguments are.

That having been said, I think it would be easiest to use a macro.

For a starting point, something like this:

For i = 1 To Workbooks("Book1").Sheets.Count
    Range("A" & i) = Workbooks("Book1").Sheets(i).Name
Next i

If you wish to pursue a macro solution, please post a new thread in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

[tt]
=cell("filename",'[workbook2.xls]Sheet1'!A1)
but there's more...
=RIGHT(cell("filename",'[workbook2.xls]Sheet1'!A1),LEN(cell("filename",'[workbook2.xls]Sheet1'!A1))-FIND("]",cell("filename",'[workbook2.xls]Sheet1'!A1)))
[/tt]

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

Skip,

I still cannot get the Worksheet names to come up. When I use your parameters for the cell function a Window opens saying Update Values for WookBook2. It then results in
#N/A

Any other suggestions? Thanks for your help.

 

[SkipVought]

This does not "get the Worksheet names to come up"

It gets the ONE worksheet name for the cell that you REFERENCE in the CELL function. You would need to do that formula for every sheet in the other workbook.

What is your objective for assembling this list?



Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

Workbook2 has worksheet names such as "Data", "Rdwy 1" and "Rwdy 2", etc. Workbook2 can have up to 20 worksheet names.

I need to list all of the Workbook2 worksheet names in a single column in Workbook1 (Sheet1). So Workbook1 cell
B1 ="Data", B2 = "Rdwy 1", B3 = Rwdy 2", etc.

I am using the values in Workbook1 for indirect functions and will be changing the Workbook2 name depending on which spreadsheet I need to list worksheet names.

Thanks for helping.

 

[SkipVought]

Here's some quick VBA code. If you need to pursue this code further, please post in Forum707.

Sub ListSheetsInOtherWorkbooks()
'puts other workbook/sheet names into the ActiveSheet!A1, starting in the first empty row.
    Dim wb As Workbook, ws As Worksheet, lRow As Long
    lRow = ActiveSheet.[A1].Rows.Count + 1
        For Each wb In Workbooks
        If ThisWorkbook.Name <> wb.Name Then
            For Each ws In wb.Worksheets
                ActiveSheet.Cells(lRow, "A").Value = wb.Name
                ActiveSheet.Cells(lRow, "B").Value = ws.Name
                lRow = lRow + 1
            Next
        End If
        Next
End Sub

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

Thanks, I will give your code a try.

I guess the cell function won't work for what I need to do.

 

[SkipVought]

Here's a better STURCTURE...

Sub ListSheetsInOtherWorkbooks()
'puts other workbook/sheet names into the ActiveSheet!A1, starting in the first empty row.
    Dim wb As Workbook, ws As Worksheet, lRow As Long
    lRow = ActiveSheet.[A1].Rows.Count + 1
    For Each wb In Workbooks
        If ThisWorkbook.Name <> wb.Name Then
            For Each ws In wb.Worksheets
                ActiveSheet.Cells(lRow, "A").Value = wb.Name
                ActiveSheet.Cells(lRow, "B").Value = ws.Name
                lRow = lRow + 1
            Next
        End If
    Next
End Sub

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[AnotherHiggins]

The problem with Cell function is that you need to use the name of the target sheet in the function, with a different function for each sheet. So you already have to have the name of every sheet.

The only good thing about using the cell function is that you could [ignore][Ctrl] + [Tab][/ignore] between the workbooks and click on each sheet with the mouse. That's probably better than manually writing the list, but still pretty darned manual. That's why I suggested a macro yesterday.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[clifftech]

I really need to come up with a function rather than a macro.


So that in Workbook1

Cell A1 = "Workbook2.xls"


Then the function in B1 would be
= ListSheetsinOtherWorkbooks(A1,Sheet1) results in "Data"

B2 would be = ListSheetsinOtherWorkbooks(A1,Sheet2) results in "Rdwy 1"

B3 would be = ListSheetsinOtherWorkbooks(A1,Sheet3) results in "Rdwy 2"


Any suggestions?

 

[SkipVought]

Just use a lookup.
[tt]
=Index(SheetX!$B$2:$B$99,[red]1[/red],1)
[/tt]
where B99 is the LAST row of sheet data in SheetX, and the second argument is the sheet number to return the name for.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

To use the index function however, I would first need to use the ListSheetsInOtherWorkbooks macro you gave me to generate SheetX data - correct?

Can I develop a vba function that simply gives me the sheet name based on two parameters - the spreadsheet file name and the sheet index number?

 

[SkipVought]

function ListSheetsInOtherWorkbooks(sWB as String, i as integer) As String
   ListSheetsInOtherWorkbooks = Workbooks(sWB).Worksheets(i)
End function

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

I feel dumb! You gave me exactly what I asked for. What I forgot to add was the function results in the name of the worksheet.

So for the function

=ListSheetsInOtherWorkbooks(Workbook2.xls, 1) results in "Data" which is the name for the first worksheet in Workbook2.xls

Thanks for staying with me on this one.

 

[SkipVought]

sorry, I forgot the NAME property.

Function ListSheetsInOtherWorkbooks(sWB As String, i As Integer) As String
   ListSheetsInOtherWorkbooks = Workbooks(sWB).Worksheets(i).Name
End Function

ALSO...
the workook name is in QUOTES unless its a cell reference.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[clifftech]

Skip,

That works! Thanks so much. I was anticipating a more complicated vba function.

If I had only asked the question correctly in the first place I would have saved everyone's time.

Thanks again.

 

[SkipVought]

Next time, if you have a VBA / macro code question, please post in Forum707.



Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}