Code to pull specific cell values from each worksheet to a summary worksheet

[donh]

I have about 1-150 worksheets in a workbook that I need specific cells pulled so I don't have to go to each worksheet to see the value that is there (all the worksheets are exactly the same)

I would like to start pulling values from the 5th worksheet to the end of the workbook

What I would like on the summary sheet is:
sheet5 tab name in D8 and then E8 would have the value from sheet5 C9,
sheet6 tab name in D9 and then E9 would have the value from sheet6 C9,
sheet7 tab name in D10 and then E10 would have the value from sheet7 C9,
to the end of the workbook

I can't make it work by recording a macro and is very time consuming to build the links manually

Can someone help?

Thanks

 

[Smitty]

You can use INDIRECT:

=INDIRECT(D8&"!C9")

Where D8 has the sheet name you want to reference.

HTH,

 

[donh]

I am not having any luck with this - - - I am working inside the same workbook

I get a #ref! error - - - when I replace the " & " with a " , " then I get a #value! error

 

[Smitty]

The formula I wrote is for the same workbook.

Why are you replacing the & with a comma? The ampersand is a specific part of the formula that's required.

If your sheet names have spaces in them you'll need to adjust the formula just a bit:

=INDIRECT("'"&D8&"'!C9")

(Note the addition of apostrophes to accommodate spaces in the sheet name).

 

[donh]

That worked perfectly - - - I went to excel/help to try and fix the formula and was trying different things to get it to work - - - please excuse my ignorance

for part 2 of the equation - - - I have the formulas built for the specific cells I need - - - is there a way to write a macro to copy to just the rows that have data in column C

Range("D8:M8").Select
Selection.Copy

THANKS

 

[Smitty]

I'm not quite sure what you mean for the second part.

Do you mean that you only want the INDIRECT formula to reference sheets that have data in C9?

 

[donh]

I'm sorry - - - I want to copy the formulas down the rows on my summary page

So if I have 100 sheets in this workbook I will have data for each row in Column C on the summary page down to row 107 for each of the sheets (since I start on row 8) but in another workbook I might only have data in each row of Column C on the summary page to only row 10 so I only need to copy the formulas to row 17.

The only way I know how to copy in a macro is to a set range all of the time - - - is there a way to only copy the range of formulas to match if there is data present in column C?

 

[Smitty]

Maybe something like this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Sheets.Count - 1<br>            Cells(i + 8, "D").FormulaR1C1 = "=INDIRECT(RC[-1]&""!C9"")"<br>        <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>