How to make Macro for all 20 sheet that excute in one step

Question

Hello

I have made one macro to insert column and execute one formula. see below

I want to make this common for all 20 sheet in one workbook. Means this marco shall happen for all sheets in one run.

The macro for one sheet is as below..

---

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  // Insert at range F:F on selectedSheet, move existing cells right

  selectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);

  // Set range F6 on selectedSheet

  selectedSheet.getRange("F6").setFormula("=(C6+E6)/2");

  // Auto fill range

  selectedSheet.getRange("F6").autoFill("F6:F105", ExcelScript.AutoFillType.fillDefault);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalDown).setStyle(ExcelScript.BorderLineStyle.none);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalUp).setStyle(ExcelScript.BorderLineStyle.none);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setWeight(ExcelScript.BorderWeight.thin);

  // Set border for extended range obtained by extending down from range F6 on selectedSheet

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.continuous);

  selectedSheet.getRange("F6").getExtendedRange(ExcelScript.KeyboardDirection.down).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setWeight(ExcelScript.BorderWeight.thin);

}

 

---

Answer 1

Hi @Chaturvedi, Santosh,

For your concern, since you are using an Office Script, you can use the workbook.getWorksheets() to loop through all worksheets:

function main(workbook: ExcelScript.Workbook) {
  let sheets = workbook.getWorksheets();
  for (let sheet of sheets) {
  // Insert at range F:F on selectedSheet, move existing cells right
  sheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);
  // Set range F6 on selectedSheet
  sheet.getRange("F6").setFormula("=(C6+E6)/2");
  // Auto fill range
  sheet.getRange("F6").autoFill("F6:F105", ExcelScript.AutoFillType.fillDefault);
  //Remaining Scripts of your macros...

This script will loop through all worksheets and work on each sheet. If you want to run only specific 20 sheets, you need to explicitly list all the sheet names:

function main(workbook: ExcelScript.Workbook) {
  // List all the names here, I just list 5 as an example
  let targetSheetNames = ["Sheet1","Sheet2","Sheet3", "Sheet4", "Sheet5"];
  for (let name of targetSheetNames) {
  let sheet = workbook.getWorksheet(name);
  if (!sheet) continue;
  // Insert at range F:F on selectedSheet, move existing cells right
  sheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);
  // Set range F6 on selectedSheet
  sheet.getRange("F6").setFormula("=(C6+E6)/2");
  // Auto fill range
  sheet.getRange("F6").autoFill("F6:F105", ExcelScript.AutoFillType.fillDefault);
  //Remaining Scripts of your macros...

Hope this information may be helpful. If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.  

Thank you for your patience and understanding. I'm looking forward to your reply.

---

If the answer is helpful, please click "YES" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread