Loop to copy sheets multiple times with new name (date)

user_17410935 0
Viewed 81

I am working on an Excel workbook that will contain a sheet for each day, with a log of various times for work, equipment failures, etc.

This one sheet, MASTER, should be copied to 365 (366 for leap years) sheets, each named by date, from 01-01-2022 to 31-12-2022.

In one cell of each sheet, the date should be entered.

Now, I have recorded a macro that selects a sheet, copies this and moves it to the end of the row, adds the date in the appropriate cell and then renames the sheet to a date.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Duplicate worksheet
    let mASTER__2_ = selectedSheet.copy(ExcelScript.WorksheetPositionType.before, selectedSheet);
    // Reorder mASTER__2_
    mASTER__2_.setPosition(3);
    // Rename worksheet to "01-01-2022"
    mASTER__2_.setName("01-01-2022");
    let _01_01_2022 = workbook.getWorksheet("01-01-2022");
    // Set range Q1:T2 on _01_01_2022
    _01_01_2022.getRange("Q1:T2").setValue("01-01-2022");
}

What I need now is the format for the loop to repeat for each day in a given year, and how to set a variable, e.g. "myDate", that will increase with one day per iteration of the loop, instead of the hardcoded date in the example above.

While we could work in a routine to copy the master sheet at the end (or beginning) of each work day, I would like to have the full year ready in one workbook at once.

office365 typescript excel
1 Answers

This should do it.

When Setting up

  • Make sure your MASTER sheet has the first date you want to appear (i.e. 01-01-2022. in whatever cell is the date (i.e. Q1)
  • Did you mean to populate a range of cells with the date? Your question said "one cell of each sheet", while your procedure did a range of Q1:T2. See note in code.
  • Make sure the cell Q1 is actually a value (not text) and in the display format you want the sheet to appear. Based on the format in your example it would be mm-dd-yyyy.
  • Slashes / aren't allowed in sheet names, but my procedure will swap these for dashes - if you wish to use that format.

When Testing...

  • You might want to test with a number less than 365.
  • The procedure will delete and overwrite any worksheets with the previous date that exists as a sheet name.

Code:

function main(workbook: ExcelScript.Workbook) {
  const cellAddressWithDate = "Q1";
  const startingSheetName = "MASTER";
  const numberOfDays = 365;//<-- maybe start with 5?

  let startSheet = workbook.getWorksheet(startingSheetName);
  let theDate = startSheet.getRange(cellAddressWithDate).getValue();
  for (let i = 0; i < numberOfDays; i++) {
    let aCopy = startSheet.copy(ExcelScript.WorksheetPositionType.before, startSheet);
    aCopy.setPosition(startSheet.getPosition() + i);
    aCopy.getRange("Q1:T2").setValue(theDate + i);//<-- range or single cell?
    let newName = aCopy.getRange(cellAddressWithDate).getText().replace(/\//g, "-");
    workbook.getWorksheet(newName)?.delete();
    aCopy.setName(newName);
  }
}
user_117323200
Related