I have 125 sheets with every sheet displayed in the same manner. Now I would
like to copy cell B2 (and some more cells) of every 125 sheets into one new
sheet (table). As it would take too much time to copy everything manually, i
would like to know if there is a quicker way?

hope anyone can help me,

Roog

See ans in your other posting. Pls post in ONE group only

--
Don Guillett
SalesAid Software

"Roog" wrote in message
...
I have 125 sheets with every sheet displayed in the same manner. Now I
would
like to copy cell B2 (and some more cells) of every 125 sheets into one
new
sheet (table). As it would take too much time to copy everything manually,
i
would like to know if there is a quicker way?

hope anyone can help me,

Roog

One way is to use INDIRECT ..

In the summary sheet,
List the 125 sheetnames in B1 across, eg: Sheet2
List the target cell references in A2 down, eg: B2

Then place this in B2:
=INDIRECT("'"&B$1&"'!"&$A2)
Copy B2 across and down to return the required results. Ensure the
sheetnames entered in B1 across are consistent with what's on the tabs, ie
match exactly except for case. Watch out for typos, extra spaces, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roog" wrote:
I have 125 sheets with every sheet displayed in the same manner. Now I would
like to copy cell B2 (and some more cells) of every 125 sheets into one new
sheet (table). As it would take too much time to copy everything manually, i
would like to know if there is a quicker way?

hope anyone can help me,

Roog

Roog

Sub CreateListOfB2FirstSheet()
Dim WS As Worksheet
Sheets.Add.Name = ("newsheet")
For i = 1 To Worksheets.Count
With Worksheets("newsheet")
Set WS = Worksheets(i)
.Cells(i, 1).Value = WS.Range("B2").Value
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 06:53:00 -0800, Roog wrote:

I have 125 sheets with every sheet displayed in the same manner. Now I would
like to copy cell B2 (and some more cells) of every 125 sheets into one new
sheet (table). As it would take too much time to copy everything manually, i
would like to know if there is a quicker way?

hope anyone can help me,

Roog