[Solved] Calc: Auto-increment cell reference

[belchergb]

In a macro, I want to fetch the contents of a cell containing a formula with a cell reference and copy it to the next row down. I want the reference to automatically increment to the next row.

For example, in cell B6 there is a formula, "=A6". I want the macro to automatically increment the row reference and store "=A7" in the next row down. Can you point me in the right direction to accomplish that?

[RusselB]

I know you asked for this in a macro, but what you're asking for can be easily done without a macro.
Simply enter the initial formula, as you state in your example, then copy the formula in the cell B6 and paste it into the other cell(s) that you want the incremented formula in.
If you don't use absolute cell references, then the row number and column letter will adjust automatically for the pasted formulas.

As to doing this with a macro, I don't have a clue.

[belchergb]

Thanks for that info, RusselB, but I already knew that. For other reasons I specifically want to do it in a macro. It is exactly that "automatic" behavior of calc that I want to do "manually" in a macro. How can I grab a value in a cell, paste it in the next row (things I already know how to do), and have references in it "automatically" incremented as you describe?

[robleyd]

There is a python macro in this topic which you might be able to use as a basis to create your own macro. It may be that the macro will do what you want "out of the box" without modification.

[Villeroy]

Copy a cell to a range: viewtopic.php?f=20&t=95648&p=456566#p456566

[belchergb]

Thank you Villeroy. The copyRange method of sheet is exactly what I need. It takes care of the magical adjustment of cell references in the formula. Thanks for this pointer.