How do I copy from a filtered sheet and paste in a different sheet using Excel Automate Code Editor?

Question

My issue is based on Excel's new Automate/Code Editor feature.

I am trying to copy a filtered range from one sheet to another by using the getLastRow() function to determine the range of the filtered data. But the function keeps returning the last row of the unfiltered sheet instead. So when I copy and paste the data, it just pastes the entire unfiltered sheet range instead.

I can't seem to find a solution to only copy and paste the filtered data instead. Would appreciate any help on this! My current code can be found below! :')

function main(workbook: ExcelScript.Workbook)
{
    // Filter Database sheet to find applicants who passed up till FEAST II.
    let databaseSheet = workbook.getWorksheet("Database");
    let databaseSheetRange = databaseSheet.getUsedRange();
    
    let autoFilter = databaseSheet.getAutoFilter();
    const filterCriteria: ExcelScript.FilterCriteria =
    {
      filterOn: ExcelScript.FilterOn.custom,
      criterion1: "Pass ATS's Assessment"
    };
    autoFilter.apply(databaseSheetRange, 15, filterCriteria);


    //
    let finalInterviewSheet = workbook.getWorksheet("Final Interview");
    let finalInterviewSheetRange = finalInterviewSheet.getUsedRange();
    let finalInterviewSheetLastRowIndex = (finalInterviewSheetRange.getLastRow().getRowIndex());
    
    let databaseFilteredRange = autoFilter.getRange();
    let databaseSheetFilteredLastRowIndex = (databaseFilteredRange.getLastRow().getRowIndex());

    let copyRange = databaseSheet.getRangeByIndexes(1, 0, databaseSheetFilteredLastRowIndex,3);
    let pasteRange = finalInterviewSheet.getRangeByIndexes(finalInterviewSheetLastRowIndex+1, 0, copyRange.getRowCount(), copyRange.getColumnCount());
    pasteRange.copyFrom(copyRange);
}

Answer 1

The visible range after filtering (getSpecialCells(ExcelScript.SpecialCellType.visible)) is often a non-continuous range. The script should copy each area range one by one.

function main(workbook: ExcelScript.Workbook) {
  // Filter Database sheet to find applicants who passed up till FEAST II.
  let databaseSheet = workbook.getWorksheet("Database");
  let databaseSheetRange = databaseSheet.getUsedRange();
  let autoFilter = databaseSheet.getAutoFilter();
  const filterCriteria: ExcelScript.FilterCriteria =
  {
    filterOn: ExcelScript.FilterOn.custom,
    criterion1: "Pass ATS's Assessment"
  };
  autoFilter.apply(databaseSheetRange, 15, filterCriteria);

  let finalInterviewSheet = workbook.getWorksheet("Final Interview");
  let finalInterviewSheetRange = finalInterviewSheet.getUsedRange();

  // * I guess OP only needs to copy 3 cols based on your code, modify as needed
  // get the filtered range on the first 3 cols
  let visibleRange = databaseSheet.getRangeByIndexes(1, 0, databaseSheetRange.getRowCount() - 1, 3).getSpecialCells(ExcelScript.SpecialCellType.visible);
  // loop through visible area range
  visibleRange.getAreas().forEach(areaRange => {
    // console.log(areaRange.getAddress());
    let finalInterviewSheetRange = finalInterviewSheet.getUsedRange();
    // get the first blank cell which is next to the data table
    let finalInterviewAnchorCell = finalInterviewSheetRange.getColumn(0).getLastCell().getOffsetRange(1, 0);
    // console.log(finalInterviewAnchorCell.getAddress());
    // copy the visible area range to finalInterviewSheet
    finalInterviewAnchorCell.copyFrom(areaRange)
  })
}