title: copy range and paste on next available row / but change value of first row in column b?
body: <p>I am using the following macro to copy a range and paste it onto the next available row. </p>
  
  <pre><code>Dim NextRow As Range
  Sub Save8()
  Dim sht As Worksheet, currentRow As Range
  Application.ScreenUpdating = False
  Set sht = Sheets("Time Allocation")
  Set currentRow = sht.Range(sht.UsedRange.Address)
  Set NextRow = currentRow.Offset(currentRow.Rows.Count, 0)
  
  Sheets("Time Allocation").Range("B506:L515").Copy
  'NextRow.PasteSpecial Paste:=1, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  NextRow.PasteSpecial xlPasteAll
  Application.CutCopyMode = False
  Set NextRow = Nothing
  Set currentRow = Nothing
  Set sht = Nothing
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>The code works fine however say my range i want to copy looks like this:</p>
  
  <pre><code>Example 1       Test
  Test            Test
  Test            Test
  Test            Test
  </code></pre>
  
  <p>My code will then paste the copied range below the original like so:</p>
  
  <pre><code>Example 1           Test
      Test            Test
      Test            Test
      Test            Test
  Example 1           Test
      Test            Test
      Test            Test
      Test            Test
  </code></pre>
  
  <p>But now I want to increment the value in the top left cell which is in column b 'Example 1'</p>
  
  <p>So each time the range is copied and pasted I will end up with something like:</p>
  
  <pre><code>Example 1           Test
      Test            Test
      Test            Test
      Test            Test
  Example 2           Test
      Test            Test
      Test            Test
      Test            Test
  Example 3           Test
      Test            Test
      Test            Test
      Test            Test
  </code></pre>
  
  <p>Please can someone show me how to do this? Thanks in advance</p>