copy a cell from multiple worksheet

[nick]

I cannot figure out if this is possible or not.

I have a workbook with 51 worksheets in it. The first sheet is a Total
sheet and is supposed to show a summary of each of the other 50 worksheets.
So I need to figure out how to get cell A5 from Sheet2 to show up on the
Total sheet, then in the next row I need cell A5 from Sheet3, below that cell
A5 from Sheet4, etc for all 50 worksheets.
I set the first one to "=Sheet1!$A$5" and then tried to copy it hoping it
would copy to read "=Sheet2!$A$5" but it copies as "=Sheet1!$A$5".
Any ideas on how to set it up so that I can copy the formula and have it
change the sheet name without manually typing them all in or clicking on a
box in each sheet?

 

[Gary''s Student]

Put this somewhere in the Total sheet and copy down:

=INDIRECT("Sheet" & ROWS($A$1:A1) & "!A5")

 

[Don Guillett]

If your sheets are named sheet2, sheet3, etc, this will put the formula in
col a of the active sheet

Sub getsheetformulas()
For i = 2 To Sheets.Count
Cells(i, 1).Formula = "=sheet" & i & "!a5"
Next i
End Sub

 

[nick]

That worked! Thanks!

Put this somewhere in the Total sheet and copy down:

=INDIRECT("Sheet" & ROWS($A$1:A1) & "!A5")