Autofill using sheet names

[Matt Borrill]

Looking for advice

If I have the formula =Sheet1!A1
and I want to autofill the worksheet but change sheet1 to
sheet2,sheet3,sheet4,sheet5 etc what is the best way to go
about it?

I have a workbook with >100 sheets and dont want to do it
manually.

thanks for your help

matt

 

[Mike]

I won't say this is the best way, but it is a way I would
do it:

First, a quick macro to return all the sheet names into
cells A1 to Ax of the currently selected sheet.

Sub GetSheetNames()
Row = 1
For Each S in ActiveWorkbook.Sheets
Range("A" & Row).Value = S.Name
Row = Row + 1
Next
End Sub

Then in cell B2 you can enter this formula and fill down:
=INDIRECT("'"&A1&"'!A1")

 

[Dave Peterson]

If all your worksheet names were consistent (like sheet1, sheet2, ...,
sheet100), you could use:

=indirect("sheet" & row() & "!A1")

(you may have to make it ... & row()-1 & ... if you don't start on row 1.)

and another way to get rid of the indirect:

="$$$sheet" &ROW()&"!a1"
Drag it down 100 rows.
you'll get something like:
$$$sheet1!a1
$$$sheet2!a1
$$$sheet3!a1
$$$sheet4!a1

Then copy|paste special|values
then edit|Replace
$$$ with = (equal sign)
That'll convert them to formulas.

And using Mike's idea, but dropping the formula in the cell:

Option Explicit
Sub PutFormulas()
Dim iCtr As Long
iCtr = 1
For iCtr = 1 To Worksheets.Count
If Worksheets(iCtr).Name = ActiveSheet.Name Then
'do nothing
Else
ActiveSheet.Cells(iCtr, "A").Formula _
= "='" & Worksheets(iCtr).Name & "'!a1"
End If
Next iCtr
End Sub