title: Move cell with sum function down as new entries occur using row.count
body: <p>I'm a bit surprised this has proven to be such a challenge. On <code>sheet 1</code> I have the input which looks as follows:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  erw = Sheet2.Cells(1, 1).CurrentRegion.Rows.Count + 1
  'erw.Offset(1).EntireRow.Insert Shift:=xlDow
  
  If Len(Range("c3")) &lt;&gt; 0 Then
  
  Sheet2.Cells(erw, 1) = Range("c3")
  Sheet2.Cells(erw, 2) = Range("c4")
  Sheet2.Cells(erw, 3) = Range("c5")
  
  Range("c3") = ""
  Range("c4") = ""
  Range("c5") = ""
  
  Else
      MsgBox "You must enter an amount"
  End If
  
  End Sub
  </code></pre>
  
  <p>No issues with the above, where I'm running into a problems is with the following on sheet 2 where the information is stored:</p>
  
  <pre><code>Sub AddUp()
  
  Dim rngcount As Long
  Dim TotalA As Long
  Dim rng2 As Range
  
  rngcount = Cells(Rows.Count, "A").End(xlUp).Row
  
  Set rng2 = Range("A28")
  
  TotalA = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("sheet2").Range("a1:a" &amp; rngcount))
  
  rng2 = TotalA
  </code></pre>
  
  <p>The real issue is the following <code>Set rng2 = Range("A28")</code> as this is essentially a cheat I've been using. I know that there will not be more than 26 entries to be summed and then a new sheet will be started. I currently have the <code>TotalA</code> amount <code>Set</code> to be put in <code>A28</code>, but what I am trying to do is have the <code>TotalA</code> cell move down as more entries are put in. Put another way I would rather the range where <code>TotalA</code> will be able to move as more entries are put in.   </p>
  
  <p>I began with the following <code>erw.Offset(1).EntireRow.Insert Shift:=xlDow</code>but I moved away from that as the insertion of a row needs to occur on <code>sheet2</code>I've left it here for this posting in case there is any valuable feed back.</p>
  
  <p>What I've been focusing on instead is trying to use <code>CurrentRegion.offset(1)</code> to keep moving the cell that holds the sum function down. Problem is I cannot figure out how to declare a range based on <code>rngcount</code> This may be the problem because perhaps I should not be using <code>rngcount</code> as it is not an <code>object</code>, but my thinking is/was that I could turn that <code>rngcount</code> into an object and then use <code>CurrentRegion.offset(1)</code> A bit long winded, hope the goal comes through clearly. Thanks</p>
  
  <p><a href="https://i.stack.imgur.com/nsvDb.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nsvDb.jpg" alt="Image 1"></a></p>
  
  <p><a href="https://i.stack.imgur.com/7Uwmo.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7Uwmo.jpg" alt="Image2"></a></p>