Copy a filtered table into another sheet

Question

Hi,

I have an excel worksheet with about 800 rows and 10 columns. I want to create a code which filters the table and copies the filtered data into another sheet. when I run the below code. it copies the whole 800 rows into the new sheet which is something I don't want.

function main(workbook: ExcelScript.Workbook) {
	let firstSheet = workbook.getWorksheet("Details");
	let sheetToPaste = workbook.getWorksheet("Sheet3")
	let table1 = workbook.getTable("Details")
		// Clear auto filter on selectedSheet
	firstSheet.getAutoFilter().clearCriteria();
	// Apply values filter on selectedSheet
	firstSheet.getAutoFilter().apply(firstSheet.getAutoFilter().getRange(), 9, { filterOn: ExcelScript.FilterOn.values, values: ["PM"] });
	const source = firstSheet.getRange().getVisibleView().getRange();
	// Copy the data into the other sheet.
	sheetToPaste.getRange("A1").copyFrom(source, ExcelScript.RangeCopyType.values,true,false);

Another trick I tried is changing the rangecopytype to all. The result is that I get the filtered table but with hidden rows. 800 rows still.

Appreciate your help on this.

Meriem

Answer 1

Hi,

You can modify your code as per the code below-

function main(workbook: ExcelScript.Workbook) {
  let firstSheet = workbook.getWorksheet("Details");
  let sheetToPaste = workbook.getWorksheet("Sheet3");
  let table1 = workbook.getTable("Details");
  
  // Clear auto filter on selectedSheet
  firstSheet.getAutoFilter().clearCriteria();
  
  // Apply values filter on selectedSheet
  firstSheet.getAutoFilter().apply(firstSheet.getAutoFilter().getRange(), 9, { filterOn: ExcelScript.FilterOn.values, values: ["PM"] });
  
  const filteredRange = firstSheet.getAutoFilter().getRange().getSpecialCells(ExcelScript.SpecialCellType.visible);
  
  // Copy the filtered data into the other sheet.
  sheetToPaste.getRange("A1").copyFrom(filteredRange, ExcelScript.RangeCopyType.values, true, false);
}

The getSpecialCells method with SpecialCellType.visible retrieves only the visible cells within the filtered range. By using this filtered range, you can copy only the visible data into the other sheet.

Make sure to replace "Details" and "Sheet3" with the actual names of your worksheets in the getWorksheet calls.

Give this updated code a try.

Best Regards.