title: How to use vba to expand data ranges and copy and paste into mastersheet
body: <p>This code aims to expand the context of importing copied data from multiple sources and paste in mastersheet. It also functions by being able to copy and paste from a internal master file and then use it to expand on new ranges.</p>
  
  <pre><code>Sub newloopfilemodule()
  
      Dim wb As Workbook
      Dim myPath As String
      Dim myFile As String
      Dim myExtension As String
      Dim FldrPicker As FileDialog
  
      'First clear any original data
      Sheet1.Rows("2:50").ClearContents
  
      'Optimize Macro Speed
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Application.Calculation = xlCalculationManual
  
      'Retrieve Target Folder Path From User
      Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
      With FldrPicker
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
          If .Show &lt;&gt; -1 Then GoTo NextCode
          myPath = .SelectedItems(1) &amp; "\"
      End With
  
      'In Case of Cancel
      NextCode:
      myPath = myPath
      If myPath = "" Then GoTo ResetSettings
  
      'Target File Extension (must include wildcard "*")
      myExtension = "*.xls*"
  
      'Target Path with Ending Extention
      myFile = Dir(myPath &amp; myExtension)
  
      'Loop through each Excel file in folder
      Do While myFile &lt;&gt; ""
          'Set variable equal to opened workbook
          Set wb = Workbooks.Open(Filename:=myPath &amp; myFile)
          'Ensure Workbook has opened before moving on to next line of code
          DoEvents
  
          'Identify column number for Customer Parent ID, Country, and Region
          Dim custParentIDCol As Integer, custcidcol As Integer, customernamecol As Integer
          custParentIDCol = WorksheetFunction.Match("Customer Parent ID", wb.Sheets(1).Rows(1), 0)
          custcidcol = WorksheetFunction.Match("Customer CID", wb.Sheets(1).Rows(1), 0)
          customernamecol = WorksheetFunction.Match("Customer Name", wb.Sheets(1).Rows(1), 0)
  
          'Count total number of rows in raw data file
          Dim rowNum As Integer
          rowNum = 2
  
          Dim topClients As String
  
          Dim filenamenow As String
          filenamenow = Mid(myFile, 1, InStr(1, myFile, ".") - 1)
  
  
      Dim outputrownum As Integer
      outputrownum = WorksheetFunction.CountA(Sheet1.Range("A:A"))
      outputrownum = outputrownum + 1
  
  
  
          Do While IsEmpty(wb.Sheets(1).Cells(rowNum, custParentIDCol)) = False
  
              If WorksheetFunction.CountIf(wb.Sheets(1).Range(wb.Sheets(1).Cells(2, custParentIDCol), wb.Sheets(1).Cells(rowNum, custParentIDCol)), _
                                      wb.Sheets(1).Cells(rowNum, custParentIDCol)) = 1 Then
  
          Sheet1.Cells(outputrownum, 1) = outputrownum - 1
          Sheet1.Cells(outputrownum, 2) = filenamenow
          Sheet1.Cells(outputrownum, 3) = wb.Sheets(1).Cells(rowNum, custParentIDCol)
          Sheet1.Cells(outputrownum, 4) = wb.Sheets(1).Cells(rowNum, custcidcol)
          Sheet1.Cells(outputrownum, 5) = wb.Sheets(1).Cells(rowNum, customernamecol)
  
                  If WorksheetFunction.CountIf(Sheet2.Columns(1), wb.Sheets(1).Cells(rowNum, custParentIDCol)) &gt; 0 Then
                          topClients = WorksheetFunction.VLookup(wb.Sheets(1).Cells(rowNum, custParentIDCol), Sheet2.Range("A:B"), 2, 0)
                           Sheet1.Cells(outputrownum, 6).Value = topClients
                          End If
  
                          outputrownum = outputrownum + 1
             End If
      rowNum = rowNum + 1
      Loop
  
  
          'Save and Close Workbook
          wb.Close SaveChanges:=True
          'Ensure Workbook has closed before moving on to next line of code
          DoEvents
          'Get next file name
          myFile = Dir
      Loop
  
      'Reset Macro Optimization Settings
      ResetSettings:
      Application.EnableEvents = True
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
  
      End Sub
      Sub combinedata()
  
      Dim projecttitlecol As Integer, effectivedatecol As Integer, productcol As Integer, matchingproject As Integer
          projecttitlecol = WorksheetFunction.Match("Project Title", Sheet3.Rows(1), 0)
          effectivedatecol = WorksheetFunction.Match("Effective Date", Sheet3.Rows(1), 0)
          productcol = WorksheetFunction.Match("Product", Sheet3.Rows(1), 0)
  
         Dim rowNum As Integer
         rowNum = 2
  
      Do While IsEmpty(Sheet1.Cells(rowNum, 2)) = False
         If WorksheetFunction.CountIf(Sheet3.Columns(1), Sheets(1).Cells(rowNum, 2)) &gt; 0 Then
          matchingproject = WorksheetFunction.Match(Sheet1.Cells(rowNum, 2), Sheet3.Columns(1), 0)
  
          Sheet1.Cells(rowNum, 7) = Sheet3.Cells(matchingproject, projecttitlecol)
          Sheet1.Cells(rowNum, 8) = Sheet3.Cells(matchingproject, effectivedatecol)
          Sheet1.Cells(rowNum, 9) = Sheet3.Cells(matchingproject, productcol)
  
      End If
      rowNum = rowNum + 1
      Loop
  
      End Sub
  </code></pre>