Filling cells referencing multiple sheets

[DJRenegade]

Is there an easy way to autofill cells when referencing multiple sheets, without having to change each sheet reference in the cells.

i.e In my workbook every sheet represents a day in the month. The 32nd sheet summarizes the stats from the sheets. All 31 sheets are laid out identically.
In my summary sheet column A are dates in the month Column B will refrence Cell D3 to the corresponding date.

 

[Legacy 98055]

<pre>
Hi DJ.
Found your post buried with no replies. You really need to provide some more details or else
you leave everyone to guess and a guess usually equals a waste of time. Answer these questions
and I assure you that a solution will be posted.

1. What range in Column 'A' contains your dates? (Assuming that each date refers to each individual
worksheet(1 to 31).
2. How are your sheets arranged(1 to 31 = days of the month?) Again, this is only an assumption.

I don't know of any native Excel functionality which will fill down references based upon sheet index.
This bit of code may or may not work for you. Based upon my own assumptions of your workbook's
structure. The variable "First_Row_In_Column_A" is included to make the routine easier for you to edit.
The value of 2 should be changed to the actual first row of listed dates in your summary sheet. Make sure
your summary sheet is the active sheet when running this macro.

Sub AutoFillSheetNames()
Dim i As Integer, First_Row_In_Column_A As Long
First_Row_In_Column_A = 2
For i = 1 To 31
Cells(i, 2).Value = "=" & Sheets(i).Name & "!D3"
First_Row_In_Column_A = First_Row_In_Column_A + 1
Next
End Sub

tom
</pre>

 

[DJRenegade]

Thanx for the reply Tom
Here are some more details:

Sheet 1 representing the first day of the month is named Industrial (1), sheet 2 representing the second day is named Industrial (2), and so on and so forth upto the 31st sheet.
All 31 sheets are Identically laid out.
Column A represents a different route, column I are hours of service corresponding to each route.
In my summary sheet Column A are days of the week corresponding to Dates of the Month in column B(ie. Tue-Oct1, Wed-Oct2, Thu-Oct3, etc.). Columns C to CZ are all the routes.
Cell C2 in the summary sheet looks like this ='Industrial (1)'!$I$2

Now the question is: How do I get that 1 in parenthesis to count up when I fill the subsequent cells.

 

[Legacy 98055]

I don't know DJ?
If nobody can help you, use this UDF. Paste it in any standard module. Use it like a normal function. Instead of typing in ='Industrial (1)'!$I$2 as in your example, you would type in =sh(1,"I2")
The one could be replaced with a range if you wanted.
tom

 

[Whisperer14]

The following code in Sheet 32 will copy the entry in cell A1 of each sheet into Column A of sheet 32.

Hope that helps, just change the 1 to the appropriate column number and build from there.

If you need more specific help then feel free to email me.

KR

Sub SummariseSheet()
For i = 1 To 31
Cells(i, 1) = Sheets(i).Cells(1, 1)
Next i
End Sub

 

[DJRenegade]

Thanx Tom and KR

But I knew if I just thought about it long enough the logic would come to me.
I used transpose in the paste special.
I took the reference ='Industrial (1)'!$I$2
drop the dollar sign in front of the 2 filled the subsequent cells beneath to correspond to each route
Copied then transposed then moved to the corresponding date row.
I recorded the Macro from fill to Transpose.
Created the button and voila
Now I just have to do this maneuver 30 more times as opposed to changing 3100 cells. Whew!

Thanx again
My next project is utilizing the VLOOKUP.