title: Copy/Paste Data with Cell References (VBA)
body: <p>I'm trying to copy data from one file into another. First, I'd like to find the last row/column in the source file and use that information to copy the data. However, I can't seem to get the code to copy/paste with the references to the last row and column used. Here's what I currently have:</p>
  
  <pre><code>Dim G7_Tab_Name As String
     G7_Tab_Name = "G7"
  Dim wkbDest As Workbook
     Set wkbDest = ActiveWorkbook
  Dim SheetToCopy As Worksheet
  
  LastInputRow = wkbSource.Sheets(1).Cells(Cells.Rows.Count, "A").End(xlUp).Row
  LastInputColumn = wkbSource.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
  Set SheetToCopy = wkbSource.Sheets(1)
  CopyRange = Range(SheetToCopy.Cells(LastInputRow, 1), SheetToCopy.Cells(1, LastInputColumn))
  
  'Check if any existing data
  If Len(wkbDest.Sheets(G7_Tab_Name).Range("A1").Value) &gt; 0 Then
      wkbDest.Sheets(G7_Tab_Name).UsedRange.ClearContents
      CopyRange.Copy _
      Destination:=Range(wkbDest.Sheets(G7_Tab_Name).Cells(LastInputRow, 1), Cells(1, LastInputColumn))
      Else
         CopyRange.Copy _
         Destination:=Range(wkbDest.Sheets(G7_Tab_Name).Cells(LastInputRow, 1), Cells(1, LastInputColumn))
  End If
  </code></pre>
  
  <p>I'm pretty sure that I'm not capturing the correct range in my CopyRange definition, but I don't know why. In this case, <code>lastRow=27</code> and <code>lastColumn=13</code>. So, I thought that the range would define as <code>"A1:M27"</code>.</p>
  
  <p>I'm defining these ranges as such because I need to loop through multiple files and copy data from the first sheet in each. This code will be included in a larger For loop to hit each file (once I can get it to copy/paste!)</p>