Duplicate worksheet with macro


I’m using the spreadsheet from OnlyOffice (via the kDrive infomaniak), and I would like to know how to duplicate a whole worksheet using a macro.

The use case is very basic : I have a template worksheet, which contains a lot of elements, shapes, formulas, and so on, and once a month, I would like to create a new worksheet from this template sheet.

I’ve only managed to create a blank sheet and copy/paste its content in the new sheet, but it lack of the column sizing, the objects, etc.

I didn’t find any solution in the docs.

Did I miss something, or is this impossible to do for now ?

hi @NainzguL

Unfortunately, there is no separate method to implement your scenario similar to Worksheet.Copy in Excel.
We need some time to discuss it internally. I will notify you when we have something to share.

Ok, thank you for the quick answer, I’ll do it manually for now and wait for news on that topic.


We would be delighted if you could share your efforts in creating the macro with us.
I believe many others who have the same question would be grateful for your code.

I didn’t go very far in this, but I can share with you the beginning :

    var oTechnicalSheet = Api.GetSheet("Technical");
    // Get Sheet name from a technical sheet that contains month names (in my locale)
    var sNewSheetName = oTechnicalSheet.GetRange("B5").GetValue2() + " " + oTechnicalSheet.GetRange("B6").GetValue2();
    var oCurrentMonth = Api.GetSheet(sNewSheetName);
    // If sheet don't exists, then create it
    if(oCurrentMonth === null){
        //Create sheet don't return the new sheet, it's a bit weird to me, but that's how it works
        oCurrentMonth = Api.GetSheet(sNewSheetName);

        // "Duplicate" templates
        var oTemplateSheet = Api.GetSheet("Template");

I was about to try to recover the objects from the templates using GetAllShapes(), but then I realized that I needed to create a new of each kind using AddShape(), and that’s where I left my work : translate the shapes were to much of work regarding the “right clic → Copy” on the sheet once a month :slight_smile:

I also planned to set the column width manually using SetColumnWidth() (no link for this one, I’m not allowed :frowning: ), but as for above, it was a lot of work, for something that would break every time I update the template sheet.

I hope that might helps

1 Like