Sheet names as list

  • Thread starter Thread starter Ronnie
  • Start date Start date
R

Ronnie

Is it possible to have a list on the activeworkbook of all
sheets on a specific workbook?
 
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)
 
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.
 
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)
 
...
...
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.
Click to expand...
...

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 said:
For i = 1 To Workbooks("Addins.Names v2.xls").Worksheets.Count

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

Next i
Click to expand...

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
 
Back
Top