Getting info from individual sheets into master sheet

[maurits.vandenboom]

Hi Guys,

I made a database for personal information. I gave every person one
sheet with a fixed format. I have about 200 sheets now all named
according to the individual. Now I would like to have one master sheet,
the first sheet, in the excel workbook that list all the names of the
other sheets in one column and in the next column a cell that refers to
one cell in each sheet with a remark in there. This way I have one
overview from which sheets I need to change something and which ones
are ok.

I just don't have a clue how to do this. Is there an excel function or
macro function?

Would really appreciate the help.

Regards,

Maurits

 

[vezerid]

Maurits,

It is possible to compile the list of sheet names into a master sheet
with VBA. Is the cell that you want to pick from each sheet always the
same? What do you want to store? The cell's address or its contents?

HTH
Kostis Vezerides

 

[maurits.vandenboom]

Thanks for replying,

I don't know if I can compile the names into a master sheet.
The cell I want to pich is te same.
Basically I want in column A all the names of the sheets. In Colums B I
want all values from cell D6 from all sheets.

Does that answer the questions?

Thanks for the help.
Maurits

 

[Gord Dibben]

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

Will give you the names of the sheets in column A

In B1 enter =INDIRECT(A1&"!D6")

Double-click B1 fill handle to copy down.


Gord Dibben MS Excel MVP

 

[maurits.vandenboom]

Gord,

Thank you very much!!! It works! Great!

Maurits