Cope cells

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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

 

[Gord Dibben]

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