title: Pulling data from specific workbooks and corresponding worksheets in VBA
body: <p>I have a folder that I pull data from manually but would like to automate this task. One folder has a workbook for every week for the year</p>
  
  <p>PH Daily DT WK47.xls<br>
  PH Daily DT WK48.xls<br>
  PH Daily DT WK49.xls</p>
  
  <p>In this workbook, it has info that looks like this</p>
  
  <p>Date(col1)     Value (col2)</p>
  
  <p>Monday     #</p>
  
  <p>Tuesday    </p>
  
  <p>Wednesday#</p>
  
  <p>Thursday #</p>
  
  <p>Friday   #</p>
  
  <p>Saturday #</p>
  
  <p>Sunday   #</p>
  
  <p>If I wanted to pull a value from the cell corresponding to the day in week 47 until the next monday is reached - in this case  week 48 - to continue the process, how could I go about doing this? In my head this is what Im thinking</p>
  
  <p>Pull Monday Data from PH Daily DT WK47.xls<br>
   Pull Tuesday Data from PH Daily DT WK47.xls<br>
  *<br>
  *<br>
  Pull Sunday Data from PH Daily DT WK47.xls<br>
  Pull Monday Data from PH Daily DT WK(n+1).xls</p>
  
  <p>But obviously we cant put a formula in the middle of a destination folder. Thansk for your help! Please let me know if its not clear enough and I will try my best to explain in more depth</p>