1

I work with excel 365 online. I am trying to duplicate a certain number of times a template sheet that I then have to rename by the value of a cell.

I have tried and tested several codes but currently, it returns an error on the loop and I have a problem renaming the created copy (which I think is linked to the fact that I do not activate the right tab). I am having trouble finding documentation on excel scripts so I am a little lost.

A little help would be appreciated!

function main(workbook: ExcelScript.Workbook) {
    let modele = workbook.getWorksheet("Semaine 36");

    for (i = 1 to 5 );
    // Duplicate worksheet
    let modele_copie = modele.copy(ExcelScript.WorksheetPositionType.after, modele);
    let sheet = workbook.getActiveWorksheet();
    const cellule = sheet.getRange('A3');
    const valeur = cellule.getValue();
    sheet.setName(valeur) 
    next i;
}
Improve this question
0

3 Answers 3

Reset to default
2

  • Consider incorporating exception handling in the code, for example: if a workbook contains a sheet with the same name (below script will delete existing sheets with the same name), and if the sheet name list contains empty cells (skip those cells) etc.

  • The script dynamically locate the work sheet name list on Sheet1.

function main(workbook: ExcelScript.Workbook) {
    const startRow = 3; // row# of the first sht name
    const semSht = workbook.getWorksheet("Semaine 36"); // get worksheet object
    const wsSht1 = workbook.getWorksheet("Sheet1")
    // get row# of the last name on Sheet1, index is zero-base
    const lastRow = wsSht1.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
    if (lastRow >= startRow) {
        // get all sht names
        const shtNames = wsSht1.getRange(`A${startRow}:A${lastRow}`).getTexts();
        // loop through each name
        shtNames.forEach(sName => {
            let shtName = sName[0]; // get the sht name
            if (shtName) { 
                // try to get the sht object
                let newSht = workbook.getWorksheet(shtName);
                if (newSht) { // remove sht if exists
                    newSht.delete();
                }
                // copy and rename template sht
                semSht.copy(ExcelScript.WorksheetPositionType.after, workbook.getLastWorksheet()).setName(shtName);
            }
        }
        )
    }

}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Rename Copies of a Template Using a List

  • Title: Copy Template
  • Description: Copies a template worksheet as many times as there are cells in the lookup range.
  • Note that I'm new in Office Scripts. I spent hours going through dozens of posts to create this (not complaining, just emphasizing how much of a noob I am).

enter image description here

Short

function main(wb: ExcelScript.Workbook) {
    // Return the values of the lookup range in an array.
    let lArr = wb.getWorksheet("Sheet1").getRange("A3:A7").getValues();
    // Referemce the template.
    let tws = wb.getWorksheet("Semaine 36");
    // Use each value in the array to rename a copy of the template
    // created after the last worksheet.
    for (let i = 0; i < lArr.length; i++) {
        tws.copy(ExcelScript.WorksheetPositionType //
            .after, wb.getLastWorksheet()).setName(lArr[i].toString());
    };
}

Educational

function main(wb: ExcelScript.Workbook) {
    // Define constants.
    const LOOKUP_SHEET_NAME: string = "Sheet1";
    const LOOKUP_RANGE_ADDRESS: string = "A3:A7";
    const TEMPLATE_SHEET_NAME: string = "Semaine 36";
    // Return the values of the lookup range in an array.
    let lws = wb.getWorksheet(LOOKUP_SHEET_NAME);
    let lrg = lws.getRange(LOOKUP_RANGE_ADDRESS);
    let lData = lrg.getValues();
    // Referemce the template.
    let tws = wb.getWorksheet(TEMPLATE_SHEET_NAME);
    // Use each value in the array as a name for a copy
    // of the template created after the last worksheet.
    for (let i = 0; i < lData.length; i++) {
        let nws = tws.copy(ExcelScript.WorksheetPositionType //
            .after, wb.getLastWorksheet());
        let nwsName: string = lData[i].toString();
        nws.setName(nwsName);   
    };
}
Improve this answer

Comments

0

Your for next loop doesn't follow the JavaScript syntax rules. This script works without errors, but there is a logic flaw in the script as the cell it reads to get the name for the new tab never changes, so the script tries to use the same name for the 5 tabs over and over. Since you have not provided information on that logic I'll leave that for you to solve.

function main(workbook: ExcelScript.Workbook) {
    let modele = workbook.getWorksheet("Semaine 36");

    for (let i = 1; i<5; i++){
    // Duplicate worksheet
    let modele_copie = modele.copy(ExcelScript.WorksheetPositionType.after, modele);
    let sheet = workbook.getActiveWorksheet();
    const cellule = sheet.getRange('A3');
    const valeur = cellule.getValue().toString();
    Sheet.setName (valeur)
    }
}
Improve this answer

5 Comments

I imagined that the loop problem came from the fact that I was not activating the right sheet (the copied one) but I don't know how I can do it
What is the logic that determines the names of the new sheets?
I retrieve the value of cell A3 (which corresponds to the calculation of the week number)
But how does that value change within that loop? As it is now, you get 5 times that same value from A3
No, because I calculate the week number on each sheet using the SHEET() function in particular.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.