Each time the macro is run the column specified in Next Col will be copied from the colum to the left of it, starting from position Next Row for the number of rows in Count. When the macro is done it updates the Next Col field so that the entire operation shifts one position to the right.
There is one thing that you must keep in mind. When you copy a formula this way the formula retains is fixed cell coordinates i.e. when you copy a formula to the right manually then Excel offsets to the right automatically. Using the Macro, each copy that you make of the formula will continue to refer to the original formula positions, exactly as they are.
so, if the cell containing the 33 has a formula that say is A22+B22 and you copy the formula using this method, the new formula will still be A22+B22 and not B22+C22
Anyhow, this is my quick and dirty version of what I think you want to do.
Code:
1 2 3 4 5 6 7
1 Next Col Next Row Count
2 6 3 4
3 33
4 44
5 55
6 66
Code:
Sub ah_copy()
Dim CurX, NextX, NextY, NumY, nEndY
Dim i As Integer
NextX = Worksheets("Sheet1").Cells(2, 1).Value ' the X coordinate of the cell we are going to write to
NextY = Worksheets("Sheet1").Cells(2, 2).Value ' the Y coordinate of the first cell we are going to write to
NumY = Worksheets("Sheet1").Cells(2, 3).Value ' the number of vertical cells we will write
CurX = NextX - 1 ' the X coordinate of the cel where the data is now
nEndY = NextY + NumY - 1 ' the Y coordinate of the last vertical cell we will write to
For i = NextY To nEndY ' do this loop for all the cells in the vertical range starting with the specified cel
Worksheets("Sheet1").Cells(i, NextX).Formula = Worksheets("Sheet1").Cells(i, CurX).Formula ' copy the formula from left to right
Next
Worksheets("Sheet1").Cells(2, 1).Value = NextX + 1 ' update our counter so that we remember which row to do next
End Sub
Did you like this article? Share it with your favourite social network.