Office Script -> After filter select visible rows and paste next sheet lastRow concept

Question

I have S1 and S2 sheet;
S1 sheet -> S1 has two unique values that I need to filter and copy/paste visible rows to the next sheet, like ABC_1 sheet and ABC_2

S2 sheet -> S2 has the same unique values, I need to filter and paste ABC_1 last row

function main(workbook: ExcelScript.Workbook) {
let prof_Cenr = workbook.getWorksheet("Profit");
// Set range AF15 on profit_Ce
prof_Cen.getRange("AF15").setValue("");
let selectedSheet = workbook.getActiveWorksheet();
// Set range AF3 on selectedSheet

// Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("3:3"));
    // Apply custom filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 31, { filterOn: ExcelScript.FilterOn.custom, criterion1: "=*ABC DEBT PRO*" });
    let I_ABC_DEBT___Output = workbook.getWorksheet("I_DEBT _ Output");
    // Paste to range offset by 1 row(s) and 0 column(s) from edge range obtained by extending down from range A4 on I_AGE_DEBT___Output from range 5:9 on selectedSheet
    I_AGED_DEBT___Output.getRange("A4").getRangeEdge(ExcelScript.KeyboardDirection.down).getOffsetRange(1, 0).copyFrom(selectedSheet.getRange("5:9"), ExcelScript.RangeCopyType.all, false, false);
    // Clear auto filter on selectedSheet
    selectedSheet.getAutoFilter().clearCriteria();
    // Apply custom filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 31, { filterOn: ExcelScript.FilterOn.custom, criterion1: "=*ISPEC DEBT*" });
    let I_SPECIFIC_BAD_DEBT_Output = workbook.getWorksheet("I_SPEC_DEBT_Output");
    // Paste to range A11 on I_SPEC_DEBT_Output from range 10:12 on selectedSheet
    I_SPEC_DEBT_Output.getRange("A11").copyFrom(selectedSheet.getRange("10:12"), ExcelScript.RangeCopyType.all, false, false);
    // Clear auto filter on selectedSheet
    selectedSheet.getAutoFilter().clearCriteria();

}

Need advise on how to copy visible rows and paste to the last row of the next sheet.

taller_ExcelHome · Answer 1 · 2023-07-24T06:19:00.630

You can get the visible rows with getVisibleView. Note: The getVisibleView() method returns a RangeView object, not a Range object.

Copy visible rows from activesheet to sheet Data.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let dataTable = selectedSheet.getTables()[0];
    let visRange = dataTable.getRangeBetweenHeaderAndTotal().getVisibleView();
    let source = selectedSheet.getRanges(visRange.getCellAddresses().toString());
    let targetSheet = workbook.getWorksheet("Data");
    let lastCell = targetSheet.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up);
    let rowIdx = lastCell.getRowIndex();
    if (lastCell.getText().length === 0) { rowIdx++ }
    else { rowIdx += 2 };
    targetSheet.getRange("A" + rowIdx).copyFrom(source, ExcelScript.RangeCopyType.all);
}

Office Script -> After filter select visible rows and paste next sheet lastRow concept

1 Answers1