Prev: Excel Formula
Next: CONCATENATE by criteria and range
From: DDavid on 26 Apr 2010 16:06
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.
From: Gord Dibben on 26 Apr 2010 16:29
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 <arturo.cohen(a)gmail.com>
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.

From: DDavid on 26 Apr 2010 20:40
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 <arturo.co...(a)gmail.com>
> 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.

 | 
Pages: 1
Prev: Excel Formula
Next: CONCATENATE by criteria and range