I had another interesting question involving copying and pasting some values over 1500 rows so I generated the code in VBA and decided to post it here.

Here is the “pseudocode” (my though process, which you should do in order to structure your thoughts):

1. loop through the cells
2. select c13:L16 -> copy
3. count 6 rows -> paste
4. repeat until row 1213

A range copy and paste in this case works because there are absolute references.

The formula will be update to the current row when the past is made, but when the “$” is used, an absolute reference is made, and we will be referencing just that cell, so everything will calculate correctly.

In this case, row 12 in D12 will change (it’s relative (like the culture likes to believe 🙂 )), but we row 5 WILL NOT change because it is absolutely referenced

Anyway here is how it turns out all together:

Sub CopyAndPaste()
    Dim intRow As Integer
    Dim rngSelection As Range
    Dim intCounter As Integer

    Set rngSelection = Sheets("Costs").Range("c13:L16")
    rngSelection.Select
    rngSelection.Copy
    
    'initialize intCounter
    intCounter = 12 + 6
    
    For intRow = 12 To 1213

        If intRow = intCounter Then
            Sheets("Costs").Range("C" & intRow).Select
            
            intCounter = intRow + 5

            ActiveSheet.Paste

        End If
        
    Next
End Sub

Questions?