CODEWITHSUNDEEP
excelpower-automateoffice-scripts
Luke K
Luke K

Reputation: 3

Office Script to Copy & Paste Range After Last Row

I am trying to run an office script that will copy a range consisting of the 44 rows above the last row, and paste that row one row below the last row. I see no visible errors in the script, but repeatedly does not produce the intended result and states that the "RangeByIndexes" parameter is out of range.

Here is the code I currently have. I have also specified the column count as 27. The problematic message received is tied to the fifth line.

`Designate workbook` function main(workbook: ExcelScript.Workbook) { 
`Designate Worksheet` const Sheet = workbook.getActiveWorksheet();   
`Define last row` let lastRow = Sheet.getUsedRange().getLastRow().getRowIndex();   
`log`console.log   
`copy range above last row` let sourceRange = Sheet.getRangeByIndexes(lastRow - 44, 1, 44, 28);   
`get destination range` let destRange = Sheet.getRangeByIndexes(lastRow + 1, 0, 1, 28);   ``Paste  copied range - all`` destRange.copyFrom(sourceRange,ExcelScript.RangeCopyType.all,false,false);   }

Upvotes: 0

Views: 666

Answers (1)

taller
taller

Reputation: 18778

  • Using getRangeByIndexes will raise an error if the lastRow is smaller than 44. Adding code to handle exception.

  • Defining destRange with the top-left cell is sufficient for the copyFrom method.

function main(workbook: ExcelScript.Workbook) {
  const selectedSheet = workbook.getActiveWorksheet();
  let lastRow = selectedSheet.getUsedRange().getLastRow().getRowIndex();
  let startRow = lastRow - 44;
  if(startRow<0){startRow=0};
  let sourceRange = selectedSheet.getRangeByIndexes(startRow, 0, 44, 28);
  let destRange = selectedSheet.getCell(lastRow + 1, 0);
  destRange.copyFrom(sourceRange, ExcelScript.RangeCopyType.all, false, false);
}

Note: The column index is zero-based. Please double-check the second parameter to determine if it should be 0 or 1 accordingly.

let sourceRange = Sheet.getRangeByIndexes(lastRow - 44, 1, 44, 28)

Microsoft reference document:

ExcelScript.Worksheet interface

Upvotes: 0

Related Questions