Sheet names as list

[Ronnie]

Is it possible to have a list on the activeworkbook of all
sheets on a specific workbook?

 

[Frank Kabel]

Hi Ronnie
have a look at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Frank

 

[Bob Phillips]

Ronnie,

With VBA

For Each sh In ACtiveworkbook.Worksheets
Cells(i,1).Value = sh.Name
i=i+1
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ronnie]

This is returning a '400 error'

If I wanted to refer to another workbook instead of the
Activeworkbook would i just use:

Dim sheet_name as workbook

For Each Sh in sheet_name.Worksheets
Cells(i,1).Value = sh.Name
i=i+1
Next sh

thanks for your time.

 

[Bob Phillips]

Ronnie,

This is how you would do that

i = 1
For Each sh In Workbooks("Addins.Names v2.xls").Worksheets
Cells(i, 1).Value = sh.Name
i = i + 1
Next sh

the lack of i=1 may have been your original problem.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

...
...

This is how you would do that

i = 1
For Each sh In Workbooks("Addins.Names v2.xls").Worksheets
Cells(i, 1).Value = sh.Name
i = i + 1
Next sh

the lack of i=1 may have been your original problem.

...

An alternative approach would be


i = 0

For Each sh In Workbooks("Addins.Names v2.xls").Worksheets

i = i + 1
Cells(i, 1).Value = sh.Name

Next sh


In which case the 'i = 0' statement may be unnecessary if i were an as-yet
unused integer. As an added bonus, i equals the number of entries after the loop
completes. But the count already exists, so why not use it?


For i = 1 To Workbooks("Addins.Names v2.xls").Worksheets.Count

Cells(i, 1).Value = sh.Name

Next i

 

[Harlan Grove]

For i = 1 To Workbooks("Addins.Names v2.xls").Worksheets.Count

Cells(i, 1).Value = sh.Name

Next i

Yes, indeed, copy & pase is a @#$%&! Make that


With Workbooks("Addins.Names v2.xls")
For i = 1 To .Worksheets.Count
Cells(i, 1).Value = .Worksheets(i).Name
Next i
End With