title: Expand range to an undefined range
body: <p>I have a large number of xml files in one folder.</p>
  
  <p>The below code, looks for specific xml files by searching a field in the XML file and comparing it to a range in the Excel sheet. </p>
  
  <p>It then copies the matching xml files to a new folder and renames them for readability.</p>
  
  <p>Everything is working, except very slow.</p>
  
  <p>It looks at a very small range. I would like to extend the range to a pivot table with values from F4 and down, if there is data in the cells.</p>
  
  <pre><code>Global so1, so2, so3, so4, so5, so6, so7, so8, so9, so10, so11, so12, so13, so14, so15, so16, so17, so18 As String
  Global Myfile As String
  Global WholeOrderNumber As String
  Global NewFile As String
  Global Myfiletemp As String
  Global FileName As String
  Global TempFolder As String
  Global OrderNumber As String
  Global TempOrdernumber As String
  Global TempMonth As String
  Global Month As String
  
  Sub Find_Delivery_XML()
  '******************************************************************
  'loop trough folder
  'and locate ordre numbers in XML Files in range "C4" to "C21"
  'if found then call loadXML and copyit to copy the files
  '******************************************************************
   Application.EnableEvents = True
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
  
  Worksheets("Main").Select ' pick Main sheet
  so1 = Range("C4")
  so2 = Range("C5")
  so3 = Range("C6")
  so4 = Range("C7")
  so5 = Range("C8")
  so6 = Range("C9")
  so7 = Range("C10")
  so8 = Range("C11")
  so9 = Range("C12")
  so10 = Range("C13")
  so11 = Range("C14")
  so12 = Range("C15")
  so13 = Range("C16")
  so14 = Range("C17")
  so15 = Range("C18")
  so16 = Range("C19")
  so17 = Range("C20")
  so18 = Range("C21")
  
  'In Case of Cancel
  NextCode:
  
    TempFolder = "C:\xml_found\"
    myPath = "C:\xml_all\"
    myExtension = "DK2W_PJ_SO_*.xml*"
    Myfile = Dir(myPath &amp; myExtension)
  
  'Loop through each file in folder
    Do While Myfile &lt;&gt; ""
    Myfiletemp = "C:\xml_all\" &amp; Myfile
  
  loadXML
  
  If s18 = OrderNumber Then
    copyit
  End If
  If so17 = OrderNumber Then
    copyit
  End If
  If so16 = OrderNumber Then
    copyit
  End If
  If so15 = OrderNumber Then
    copyit
  End If
  If so14 = OrderNumber Then
    copyit
  End If
  If so13 = OrderNumber Then
    copyit
  End If
  If so12 = OrderNumber Then
    copyit
  End If
  If so11 = OrderNumber Then
    copyit
  End If
  If so10 = OrderNumber Then
    copyit
  End If
  If so9 = OrderNumber Then
    copyit
  End If
  If so8 = OrderNumber Then
    copyit
  End If
  If so7 = OrderNumber Then
    copyit
  End If
  If so6 = OrderNumber Then
    copyit
  End If
  If so5 = OrderNumber Then
    copyit
  End If
    If so4 = OrderNumber Then
  copyit
  End If
  If so3 = OrderNumber Then
    copyit
  End If
  If so2 = OrderNumber Then
    copyit
  End If
  If so1 = OrderNumber Then
    copyit
  End If
  
  DoEvents
  
  Myfile = Dir
  
    Loop
  
    MsgBox "Done"
  
  ResetSettings:
    'Reset Macro Optimization Settings
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
  
  End Sub
  
  
  Sub loadXML()
  '******************************************************************
  'load XML files and get ordernumber from XML files
  ' located in = xobject.ChildNodes.Item(1).Text
  '******************************************************************
  
  Dim strPath As String
  Dim XDoc As Object
  Set XDoc = CreateObject("MSXML2.DOMDocument")
  XDoc.async = False
  XDoc.validateOnParse = False
  XDoc.Load (Myfiletemp)
  Set xObjDetails = XDoc.ChildNodes(0)
  Set xobject = xObjDetails.FirstChild
  TempOrdernumber = xobject.ChildNodes.Item(1).Text
  TempMonth = xobject.ChildNodes.Item(2).Text
  OrderNumber = Mid(TempOrdernumber, 8, 7)
  WholeOrderNumber = TempOrdernumber
  Month = Mid(TempMonth, 4, 2)
  NewFile = WholeOrderNumber &amp; "_" &amp; Mid(Myfiletemp, 24, 27)
  
  End Sub
  
  
  Sub copyit()
  '******************************************************************
  'copy files to DIR "C2" and rename them to the new filename "NewFile"
  'NewFile = WholeOrderNumber + "_" + last 27 characters of Myfiletemp
  '******************************************************************
      Dim oFSO As Object
      Set oFSO = CreateObject("Scripting.FileSystemObject")
      Call oFSO.CopyFile(Myfiletemp, TempFolder &amp; Range("C2") &amp; "\" &amp; NewFile, True)
  
  End Sub
  </code></pre>