title: How to copy data from another workbook according to Inputbox and cell coordinates?
body: <p>I am very new to VBA and need help to work on a code that will automatically synthesize data from other workbooks. </p>
  
  <p>The idea is that I have different files for each month (e.g. 2019-04) and in each file there are several workbooks for the different portfolios that I need to deal with. </p>
  
  <p>I am looking to synthesize the data in the following way: each row represents a portfolio and the columns represent the data I wish to copy from other files. Every 7 columns are specific to one particular month. </p>
  
  <p>In the code I have so far, I prompt the user to enter the date of the portfolio under the following form : YYYY-MM ; I then want to find the column of the cell that has this particular date (this part I haven’t been able to figure out yet).</p>
  
  <p>I then use a loop that will open every workbook from which I want to copy the information.</p>
  
  <p>And finally, it will copy the data from every workbook based on the row of the portfolio and the column of the date.</p>
  
  <p>Any help would be much appreciated as I have only just found out about VBA so I h=am not yet very familiar with all the tricks and tips.</p>
  
  <p>Here is what I have so far (note that I have not entered all 7 values I want to copy):</p>
  
  <pre><code>Sub StressTest()
  
  Dim index As Integer
  Dim dateColumn As Integer
  Dim portfolioName As Variant
  Dim portfolioDate As Variant
  
  portfolioDate = InputBox("Please enter date under the following form : YYYY-MM", "Date at the time of Stress Test", "Type Here")
  For index = 3 To 39
  
  portfolioName = Range("A" &amp; index &amp; " ").Value
  
  Workbooks.Open "G:\Risk\Risk Reports\VaR-Stress test\" &amp; portfolioDate &amp; "\" &amp; portfolioName &amp; ""
  
  Cells(index, dateColumn).Value = Workbooks("" &amp; portfolioName &amp; "").Worksheets("VaR Comparison").Range("B19") / Workbooks("" &amp; portfolioName &amp; "").Worksheets("Holdings - Main View").Range("E11")
  
  Cells(index, dateColumn + 1).Value = (Cells(index, dateColumn) - Cells(index, dateColumn + 6)) / Cells(index, dateColumn + 6)
  
  Cells(index, dateColumn + 2).Value = Workbooks("" &amp; portfolioName &amp; "").Worksheets("Holdings - Main View").Range("E11")
  
  Cells(index, dateColumn + 3).Value = (Cells(index, dateColumn + 2) - Cells(index, dateColumn + 9)) / Cells(index, dateColumn + 9)
  
  
  
  Next index
  
  End Sub
  </code></pre>