title: Looping through each worksheet looking for data and returning it to specific sheet if its there
body: <p>For the background on what I am creating it is a workbook to track projects. Right now I have 3 projects in my workbook but this number will be over 50 and growing. Each sheet contains project information <a href="http://i.stack.imgur.com/2o2o5.png" rel="nofollow">Project info Worksheet</a> . The code I have doesn't work right now. This picture shows what I want it to feed to <a href="http://i.stack.imgur.com/qDFPj.png" rel="nofollow">All projects page</a> this all projects page. What I want the code to do is read through each sheet and if $A$5 = "Project # :" is true then it will copy specified cells from that sheet to the all projects page. Then goes to the next sheet and does the same and if its true again, puts the values 1 row below the last one.</p>
  
  <p>I guess what I was saying was a little unclear, here is the Code I'm currently using to get the output I want and have on the "All Projects" Page in the photo:</p>
  
  <pre><code>Sub All_Data()
  
  'Sheet "1"
  Sheet7.Range("A2").Select
  ActiveCell.Formula = "=1!$F$1" 'classifying number
  Sheet7.Range("B2").Select
  ActiveCell.Formula = "=1!$B$5" 'Project #
  Sheet7.Range("C2").Select
  ActiveCell.Formula = "=1!$A$1" 'Project Name
  Sheet7.Range("D2").Select
  ActiveCell.Formula = "=1!$B$8" 'Project Engineer
  Sheet7.Range("E2").Select
  ActiveCell.Formula = "=1!$B$6" 'Maximo Time Charge
  Sheet7.Range("F2").Select
  ActiveCell.Formula = "=1!$E$5" 'Material Forecast due date
  Sheet7.Range("G2").Select
  ActiveCell.Formula = "=1!$E$11" 'Materials Forecast Actual
  Sheet7.Range("H2").Select
  ActiveCell.Formula = "=1!$F$11" 'Forecast success
  Sheet7.Range("I2").Select
  ActiveCell.Formula = "=1!$F$12" 'Non Stores Success
  Sheet7.Range("J2").Select
  ActiveCell.Formula = "=1!$E$6" '30% Due
  Sheet7.Range("K2").Select
  ActiveCell.Formula = "=1!$E$13" '30% actual
  Sheet7.Range("L2").Select
  ActiveCell.Formula = "=1!$F$13" '30% success
  Sheet7.Range("M2").Select
  ActiveCell.Formula = "=1!$E$7" '60% due
  Sheet7.Range("N2").Select
  ActiveCell.Formula = "=1!$E$14" '60% actual
  Sheet7.Range("O2").Select
  ActiveCell.Formula = "=1!$F$14" '60% Success
  Sheet7.Range("P2").Select
  ActiveCell.Formula = "=1!$E$8" '90% due
  Sheet7.Range("Q2").Select
  ActiveCell.Formula = "=1!$E$15" '90% actual
  Sheet7.Range("R2").Select
  ActiveCell.Formula = "=1!$F$15" '90% success
  Sheet7.Range("S2").Select
  ActiveCell.Formula = "=1!$B$11" 'In-service Due
  Sheet7.Range("T2").Select
  ActiveCell.Formula = "=1!$E$16" 'In-service actual
  Sheet7.Range("U2").Select
  ActiveCell.Formula = "=1!$F$16" 'In-service Success
  
  
  'Sheet "2"
  Sheet7.Range("A3").Select
  ActiveCell.Formula = "=2!$F$1" 'classifying number
  Sheet7.Range("B3").Select
  ActiveCell.Formula = "=2!$B$5" 'Project #
  Sheet7.Range("C3").Select
  ActiveCell.Formula = "=2!$A$1" 'Project Name
  Sheet7.Range("D3").Select
  ActiveCell.Formula = "=2!$B$8" 'Project Engineer
  Sheet7.Range("E3").Select
  ActiveCell.Formula = "=2!$B$6" 'Maximo Time Charge
  Sheet7.Range("F3").Select
  ActiveCell.Formula = "=2!$E$5" 'Material Forecast due date
  Sheet7.Range("G3").Select
  ActiveCell.Formula = "=2!$E$11" 'Materials Forecast Actual
  Sheet7.Range("H3").Select
  ActiveCell.Formula = "=2!$F$11" 'Forecast success
  Sheet7.Range("I3").Select
  ActiveCell.Formula = "=2!$F$12" 'Non Stores Success
  Sheet7.Range("J3").Select
  ActiveCell.Formula = "=2!$E$6" '30% Due
  Sheet7.Range("K3").Select
  ActiveCell.Formula = "=2!$E$13" '30% actual
  Sheet7.Range("L3").Select
  ActiveCell.Formula = "=2!$F$13" '30% success
  Sheet7.Range("M3").Select
  ActiveCell.Formula = "=2!$E$7" '60% due
  Sheet7.Range("N3").Select
  ActiveCell.Formula = "=2!$E$14" '60% actual
  Sheet7.Range("O3").Select
  ActiveCell.Formula = "=2!$F$14" '60% Success
  Sheet7.Range("P3").Select
  ActiveCell.Formula = "=2!$E$8" '90% due
  Sheet7.Range("Q3").Select
  ActiveCell.Formula = "=2!$E$15" '90% actual
  Sheet7.Range("R3").Select
  ActiveCell.Formula = "=2!$F$15" '90% success
  Sheet7.Range("S3").Select
  ActiveCell.Formula = "=2!$B$11" 'In-service Due
  Sheet7.Range("T3").Select
  ActiveCell.Formula = "=2!$E$16" 'In-service actual
  Sheet7.Range("U3").Select
  ActiveCell.Formula = "=2!$F$16" 'In-service Success
  
  
  
  'Sheet "3"
  Sheet7.Range("A4").Select
  ActiveCell.Formula = "=3!$F$1" 'classifying number
  Sheet7.Range("B4").Select
  ActiveCell.Formula = "=3!$B$5" 'Project #
  Sheet7.Range("C4").Select
  ActiveCell.Formula = "=3!$A$1" 'Project Name
  Sheet7.Range("D4").Select
  ActiveCell.Formula = "=3!$B$8" 'Project Engineer
  Sheet7.Range("E4").Select
  ActiveCell.Formula = "=3!$B$6" 'Maximo Time Charge
  Sheet7.Range("F4").Select
  ActiveCell.Formula = "=3!$E$5" 'Material Forecast due date
  Sheet7.Range("G4").Select
  ActiveCell.Formula = "=3!$E$11" 'Materials Forecast Actual
  Sheet7.Range("H4").Select
  ActiveCell.Formula = "=3!$F$11" 'Forecast success
  Sheet7.Range("I4").Select
  ActiveCell.Formula = "=3!$F$12" 'Non Stores Success
  Sheet7.Range("J4").Select
  ActiveCell.Formula = "=3!$E$6" '30% Due
  Sheet7.Range("K4").Select
  ActiveCell.Formula = "=3!$E$13" '30% actual
  Sheet7.Range("L4").Select
  ActiveCell.Formula = "=3!$F$13" '30% success
  Sheet7.Range("M4").Select
  ActiveCell.Formula = "=3!$E$7" '60% due
  Sheet7.Range("N4").Select
  ActiveCell.Formula = "=3!$E$14" '60% actual
  Sheet7.Range("O4").Select
  ActiveCell.Formula = "=3!$F$14" '60% Success
  Sheet7.Range("P4").Select
  ActiveCell.Formula = "=3!$E$8" '90% due
  Sheet7.Range("Q4").Select
  ActiveCell.Formula = "=3!$E$15" '90% actual
  Sheet7.Range("R4").Select
  ActiveCell.Formula = "=3!$F$15" '90% success
  Sheet7.Range("S4").Select
  ActiveCell.Formula = "=3!$B$11" 'In-service Due
  Sheet7.Range("T4").Select
  ActiveCell.Formula = "=3!$E$16" 'In-service actual
  Sheet7.Range("U4").Select
  ActiveCell.Formula = "=3!$F$16" 'In-service Success
  
  End Sub
  </code></pre>
  
  <p>With this code I am able to assign each value to the correct place, but all that is changing from each on is the sheet number in the formula, and the row changes.</p>
  
  <p>Sheet7.Range("<strong>U4</strong>").Select
      ActiveCell.Formula = "=<strong>3!</strong>$F$16" 'In-service Success</p>
  
  <p>IS there a way I can write some sort of loop that will go through each sheet, take the specified data from the "project info" sheets and move it to the "all projects" sheet and each new sheet is assigned a new row. Each project sheet contains the same info in the same cell locations.</p>