Automate sheets list
 

Hi to all,

I have been looking on how to get this through. I have a workbook
with at least 3 sheets, that can increase to 20, 30, etc... or reduced
to 3. the first 3 sheets are always in the workbook, as standard.

The thing is that in the second sheet i want to list all the sheets
present in the workbook, as a list, and next to each one I want the
value in cell H12. Ie.:

Sheet name - total (cell H11 of each sheet)
Assy1 3.4341
Mech1 11.24508
.....
mech5 3.7654
Patt 12.6667

Can any of you give some help/clue how to get this done?
Thanks in advance.

Automate sheets list
 

Sub CreateListOfSheetsOnSecondSheet()
Dim WS As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(2)
Set WS = Worksheets(i)
.Cells(i, 1).Value = WS.Name
.Cells(i, 2).Value = WS.Range("H11")
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 13:06:37 -0700 (PDT), DDavid
wrote:

Hi to all,

I have been looking on how to get this through. I have a workbook
with at least 3 sheets, that can increase to 20, 30, etc... or reduced
to 3. the first 3 sheets are always in the workbook, as standard.

The thing is that in the second sheet i want to list all the sheets
present in the workbook, as a list, and next to each one I want the
value in cell H12. Ie.:

Sheet name - total (cell H11 of each sheet)
Assy1 3.4341
Mech1 11.24508
....
mech5 3.7654
Patt 12.6667

Can any of you give some help/clue how to get this done?
Thanks in advance.

Automate sheets list
 

Hi Gord,

Excellent, Thanks, worked like a charm! I will definitely have to
become an Excel mvp, Many thanks again

ADC

On Apr 26, 4:29*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Sub CreateListOfSheetsOnSecondSheet()
* * Dim WS As Worksheet
* * For i = 1 To Worksheets.Count
* * * * With Worksheets(2)
* * * * * * Set WS = Worksheets(i)
* * * * * * .Cells(i, 1).Value = WS.Name
* * * * * * .Cells(i, 2).Value = WS.Range("H11")
* * * * End With
* * Next i
End Sub

Gord Dibben *MS Excel MVP

On Mon, 26 Apr 2010 13:06:37 -0700 (PDT), DDavid
wrote:



Hi to all,

I have been looking on how to get this through. *I have a workbook
with at least 3 sheets, that can increase to 20, 30, etc... or reduced
to 3. the first 3 sheets are always in the workbook, as standard.




The thing is that in the second sheet i want to list all the sheets
present in the workbook, as a list, and next to each one I want the
value in cell H12. Ie.:

Sheet name * *- * total (cell H11 of each sheet)
Assy1 * * * * * * *3.4341
Mech1 * * * * * * 11.24508
....
mech5 * * * * * * *3.7654
Patt * * * * * * * * *12.6667

Can any of you give some help/clue how to get this done?
Thanks in advance.