title: How to loop function through list of aurguments in VBA
body: <p>I am looking to make this VBA function more dynamic and handle a list of inputs rather than a single static argument. The function I have is working correctly for a single hard coded file, I would like to make it loop through all files given specific criteria. The specific Criteria being: for each excel file in "C:\Sales_Reports\Special Promotions" with the name string starting with "2017 WK 9 WOW" (where the 9 could also be dynamic) call the function.</p>
  
  <p>The function currently copies 18 values from file "2017 WK 9 WOW" without opening it into a consolidated workbook 'P' (where the macro is saved). I have several file "2017 WK 9 WOW"s that do not change in structure which all need to be copied to file 'P', adding a new row with the data each time (row 5, below header).</p>
  
  <pre><code>Sub GetVal()
  
      MsgBox TheValue("C:\Sales_Reports\Special Promotions", _
          "2017 WK 9 WOW - Ladies Tee", "Week Summary")
  End Sub
  
  Function TheValue(Path, WorkbookName, Sheet) As String
  Dim testrange As Range
  ActiveSheet.rows("5:5").Insert Shift:=xlDown
  Set testrange = Range("d$15,e15,f15,t15,n15,s15,ab15,ae15,af15,aj15,ak15,am15,an15,ap15,aq15,at15,av15,bv6")
  
  For Each cell In testrange
  
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Select Case Replace(cell.Address, "$", "")
      Case "D15"
          TgtRng = "E"
      Case "E15"
          TgtRng = "F"
      Case "F15"
          TgtRng = "G"
      Case "T15"
          TgtRng = "H"
      Case "N15"
          TgtRng = "I"
      Case "S15"
          TgtRng = "J"
      Case "AB15"
          TgtRng = "K"
      Case "AE15"
          TgtRng = "L"
      Case "AF15"
          TgtRng = "M"
      Case "AJ15"
          TgtRng = "N"
      Case "AK15"
          TgtRng = "O"
      Case "AM15"
          TgtRng = "P"
      Case "AN15"
          TgtRng = "Q"
      Case "AP15"
          TgtRng = "R"
      Case "AQ15"
          TgtRng = "S"
      Case "AT15"
          TgtRng = "T"
      Case "AV15"
          TgtRng = "U"
      Case "BV6"
          TgtRng = "V"
      Case Else
          Exit Function
  
  End Select
  
  Range(TgtRng &amp; "5").Value = "='" &amp; Path &amp; "\[" &amp; WorkbookName &amp; "]" &amp; Sheet &amp; "'!" &amp; cell.Address
  
  Application.ScreenUpdating = True
  
  Next
  End Function
  </code></pre>