[Question] Macro Help

[Daksha]

Using a macro how do I copy a range of cells, with formulas, from one range to the next range/column? Then copy the new range of cells to the next column when the macro is run again?

[IMHO]

You will have to provide a bit more info, like what it is what you are trying to do, for us to understand the question. As I understand, you want to copy A1:A10 to B1:B10 and next run of the macro it must copy B1:B10 to C1:C10. Or must it select the same type of range from where your cursor is when the macro is run and paste it at a position relative to where your cursor is?

Do you know about the select, drag and release to copy and paste? You would select say A1:A10. Then look for the 'handle' at the bottom right of A10 and drag the whole selection to the right, as far as you want, and the formulas from column A will be copied to all the columns you dragged it to. You can drag horizontal or vertical. Play with it!

[adrianh]

I think that all you need to do is have a cell that retains the column number.

i.e. set up cel a1 to contain the name of the colum that you want to move. and a2 the number of cells in the range

the macro reads the starting column number from cell a1, do the copy for the number of times found in a2 and once it has done its thing rewrites a1 with the new column name.

When you run it again it will find the new starting point in cel a1 and it will still run for the correct number of iterations.

[adrianh]

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.

      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

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