Create a worksheet by selecting/filtering data in another worksheet

cu202 · March 27, 2017, 6:24am

I’ll like to create a worksheet B by selecting only data in work sheet A where values in the first column of A contain a certain value (say a value 10).

imran.rafique · March 27, 2017, 8:06am

Hi,

Thank you for contacting support. You can apply an auto filter to a range of cells in the worksheet, refresh it to get only the filtered data, and then copy the range of filtered data to the destination worksheet worksheet by specifying the destination range. We have attached the source excel file and output excel file for your kind reference.

[.NET, C#]

Workbook wb = new Workbook(“source.xlsx”);

Worksheet dstSheet = wb.Worksheets.Add(“Dest”); Worksheet ws = wb.Worksheets[0]; ws.AutoFilter.Range = “A1:H1”; ws.AutoFilter.AddFilter(2, “1”); ws.AutoFilter.AddFilter(2, “4”); ws.AutoFilter.AddFilter(2, “6”); ws.AutoFilter.AddFilter(2, “8”);
ws.AutoFilter.Refresh(); 
Range rng = ws.Cells.CreateRange(“A1”, “J11”); Range dstRng = dstSheet.Cells.CreateRange(“A1”, “J11”); PasteOptions opts = new PasteOptions(); opts.OnlyVisibleCells = true; opts.PasteType = PasteType.Values; dstRng.Copy(rng, opts);

wb.Save(“output.xlsx”);

cu202 · March 27, 2017, 8:24am

Hello Imran

Thanks, but the solution is not quite what I am looking for. I simply want to select the data in worksheet A based on a criterion (e.g. the value of Column A being 10) and use the selected data to create a new worksheet. See the attached “Source data” and “Filtered data” illustration.

imran.rafique · March 27, 2017, 8:50am

Hi,

Thank you for the inquiry. Please modify the code as below:

.NET, C#

…

Worksheet ws = wb.Worksheets[0]; ws.AutoFilter.Range = “A1:H1”;

// here 0 is the column index ws.AutoFilter.AddFilter(0, “10”);
ws.AutoFilter.Refresh();