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

donh

Board Regular
Joined
May 7, 2002
Messages
151
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You can use INDIRECT:

=INDIRECT(D8&"!C9")

Where D8 has the sheet name you want to reference.

HTH,
 

donh

Board Regular
Joined
May 7, 2002
Messages
151
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
May 7, 2002
Messages
151
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
May 7, 2002
Messages
151
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

Legend
Joined
May 15, 2003
Messages
29,536
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top