Modify Excel Macro Script to Accommodate a Dynamic Range of Data

Hello johnellis1971200,

To modify the range to extend to the last row of data in column B, you can use the getUsedRange method. This method returns the used range, which is the smallest range that encompasses any cells that have a value or formatting. Here’s how you can modify your script:

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let _2024Forecast = workbook.getWorksheet("2024Forecast");
  
  // Get the last row in column B containing data
  let lastRow = selectedSheet.getUsedRange().getRowCount();

  // Paste to range B2:B(lastRow) on selectedSheet from range A4:A(lastRow+2) on _2024Forecast
  selectedSheet.getRange(`B2:B${lastRow}`).copyFrom(_2024Forecast.getRange(`A4:A${lastRow+2}`), ExcelScript.RangeCopyType.values, false, false);
  
  // Paste to range F2:F(lastRow) on selectedSheet from range B4:B(lastRow+2) on _2024Forecast
  selectedSheet.getRange(`F2:F${lastRow}`).copyFrom(_2024Forecast.getRange(`B4:B${lastRow+2}`), ExcelScript.RangeCopyType.values, false, false);
  
  // Paste to range H2:H(lastRow) on selectedSheet from range F2:F(lastRow) on selectedSheet
  selectedSheet.getRange(`H2:H${lastRow}`).copyFrom(selectedSheet.getRange(`F2:F${lastRow}`), ExcelScript.RangeCopyType.all, false, false);
}

This script will copy the data from the “2024Forecast” sheet to the active sheet, extending to the last row of data in column B of the active sheet. Note that the getUsedRange method considers any cell with data or formatting, so if there are any formatted cells beyond your data in column B, the lastRow might be larger than expected. If this is the case, you might need to consider a different approach to find the last row with data in column B.