title: Can't get copy to find last row of data
body: <p>I had previous question on how to find the last row of data for a formula in Column A based on data in Column C.  And ya'll helped me figured that portion out!</p>
  <p>Now I'm taking it a step further, and looping through more worksheets.
  The data in Column C is Copying/Pasting correctly to the next available row.<br />
  However, the formula is pasting over the first set of data in Column A, versus finding the last row.</p>
  <p>Better yet.. is there a way to make this formula a 1 liner??  I can't seem to get that to work either.</p>
  <pre><code>`Dim Mkts As Worksheet
  Dim ws As Worksheet
  
  'Destination Worksheet
      Set Mkts = Workbooks(&quot;Nielsen SC Template.xlsm&quot;).Worksheets(&quot;Markets&quot;)
  
  'Copy 4Wk Data
  Workbooks.Open &quot;C:\Users\cday\OneDrive - udfinc.com\Nielsen Scorecard\4Wk Data.xlsx&quot;
  
      Dim Wb4 As Workbook
           Set Wb4 = Workbooks(&quot;4Wk Data.xlsx&quot;)
  
  For Each ws In Wb4.Worksheets
      With ws
          If .Index &lt;&gt; 1 Then
              
   'Find last used row in the copy range based on data in Column A
      Dim CopyLastRow4 As Long
          CopyLastRow4 = .Cells(.Rows.Count, 1).End(xlUp).Row
  
   'Find first blank row in the destination range based on data in Column C, Offset 1 Row
      Dim DestLastRowC As Long
          DestLastRowC = Mkts.Cells(Mkts.Rows.Count, &quot;C&quot;).End(xlUp).Offset(1).Row
  
   'Find last used row in the destination range based on data in Column C
      Dim LastRowColumnC As Long
          LastRowColumnC = .Range(&quot;C&quot; &amp; .Rows.Count).End(xlUp).Row
      
      If .Index = 2 Then
      'Copy and Paste Data into C3
          .Range(&quot;A4:V&quot; &amp; CopyLastRow4).Copy Mkts.Range(&quot;C&quot; &amp; DestLastRowC)
      
      'Add Dates to Column A
          Dim FR As Range    'first row
          Dim LR As Range    'last row
              Set FR = Mkts.Range(&quot;A3&quot;)
              Set LR = Mkts.Range(&quot;A&quot; &amp; LastRowColumnC - 1)
                  Range(FR, LR).Formula = &quot;=Mid('[4Wk Data.xlsx]Report1'!$A$1, 9, 28)&quot;
  
      'Add Markets to Column B
          Set FR = Mkts.Range(&quot;B3&quot;)
          Set LR = Mkts.Range(&quot;B&quot; &amp; LastRowColumnC - 1)
              Range(FR, LR).Formula = &quot;=Mid('[4Wk Data.xlsx]Report1'!$A$1, 48, 13)&quot;
      End If
      
      If .Index = 3 Then
          'Copy and Paste Data into C3
          .Range(&quot;A4:V&quot; &amp; CopyLastRow4).Copy Mkts.Range(&quot;C&quot; &amp; DestLastRowC)
      
      **'Add Dates to Column A
          Set FR = Mkts.Range(&quot;A&quot; &amp; DestLastRowC)
          Set LR = Mkts.Range(&quot;A&quot; &amp; LastRowColumnC)
              Range(FR, LR).Formula = &quot;=Mid('[4Wk Data.xlsx]Report2'!$A$1, 9, 28)&quot;
      'Add Markets to Column B
          'Set FR = Mkts.Range(&quot;B&quot; &amp; DestLastRowC)
          'Set LR = Mkts.Range(&quot;B&quot; &amp; LastRowColumnC)
              'Range(FR, LR).Formula = &quot;=Mid('[4Wk Data.xlsx]Report2'!$A$1, 48, 15)&quot;**
      End If
  
          End If
      End With
  Next ws
  </code></pre>