Macro - How to copy a line from sheet "A" to sheet "B" with all datas : formulas, value, font

Desktop editors
1

Hello

I’m quite in a dead-end here.
In a workbook, I’m trying to copy a line from sheet A to sheet B.
I want to keep all the data, formulas & parameters (ex : values controled by a list) from the source line.
Up to now, I can’t find a way to do it corrrectly, even with the help of chatGPT.

So if someone has a code sample to share, I’ll be glad to have it.

Thanks in advance :slight_smile: !

2

Thanks @Nikolas for moving my question in the right section of the forum.
I hope I’ll get more answers than in the DE section…

3

Hey @arcqus :wave:

I won’t hide it — this was generated by ChatGPT :slight_smile:
The macro should copy both the formulas and values from the range A1:C1 on Sheet1 to Sheet2.

(function() {
    // Function to copy formulas from one sheet to another
    function CopyRange() {
        // Access the sheets
        var sheet1 = Api.GetSheet("Sheet1");
        var sheet2 = Api.GetSheet("Sheet2");

        // Read formulas from range A1:C1 on Sheet1
        var formulas = [];
        for (var col = 0; col <= 2; col++) {
            var cellFormula = sheet1.GetRangeByNumber(0, col).GetFormula();
            formulas.push(cellFormula);
        }

        // Paste formulas into range A1:C1 on Sheet2
        for (var col = 0; col <= 2; col++) {
            sheet2.GetRangeByNumber(0, col).SetValue(formulas[col]);
        }
    }

    // Call the function to copy formulas
    CopyRange();
})();


Book1.xlsx (9.3 KB)

4

thanks @Nikolas !

However, in my case it is only partially working.
Some formulas (ex : column O") are not copied because they generate an error when copied with the macro (no error when copied manually) :frowning:

image
image1822×838 122 KB

Note that this not a error coming from the macro but an error from the formula itself.

Générateur de menus en anglais v2.0.2.xlsx (155.7 KB)

5

Hey @arcqus :handshake:

I need to take some time to figure out what’s happening with your macro.

Could you please let me know how to reproduce the error you’re encountering?
It would also be helpful if you could provide a video recording of the issue.

6

Hello @Nikolas
Actually, @Constantine has been smarter than chatgpt and gave me a simple solution that 1) is working and 2) allows a big simplification of the code.

So I’m moving to the next stage of my project :slight_smile:

3 Likes
7

Hello, any chance the simplified code could be included as a reply to this message? I am also trying to perform the same operation with an OnlyOffice macro.

8

Hello @qualm

Please see it here: