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?