excel - Save slicer values to worksheet -

excel - Save slicer values to worksheet -

hi having difficulty trying cycle through slicers see if filtered or not.

my goal selected slicer worksheet can apply (high low) filter underlying pivot info can pick "top 5 on budget" based upon selections made in info slicers.

i have next code see error: run time error 438’ object doesn’t back upwards method

can advise how can accomplish this.

public sub top_over_under_booked() dim osi sliceritem dim oslicercache slicercache dim osl slicercachelevel dim opt pivottable dim osh worksheet set target_ws = thisworkbook.worksheets("get slicer selections") each oslicercache in thisworkbook.slicercaches each opt in oslicercache.pivottables opt.parent.activate 'slice name worksheet_name = ucase(opt.parent.name) if worksheet_name = ucase("chart analysis 5 years") column_no = 0 slicer_name = ucase(oslicercache.name) select case ucase(oslicercache.name) case = "slicer_fy1" column_no = 1 case = "slicer_report_pt_dept1" column_no = 2 'there loads more slicer needs ne ignored. end select if column_no <> 0 each osl in activeworkbook.slicercaches(oslicercache.name) ' <----- error each osi in osl.sliceritems 'osi.selected = true check_slicer_string = osi.value 'target_ws.cells(ource_ws.cells(65000, column_no).end(xlup).row + 1, column_no) = oslicercache.value next next end if opt.parent.name end if next next end sub

there 2 different excel's

using microsoft.office.tools.excel; using excel = microsoft.office.interop.excel;

example within thisworkbook.cs

public string selectedreportcategories() { var selection = ""; var mytarget = this; seek { var caches = mytarget.slicercaches["slicer_report"]; foreach (excel.sliceritem sliceritem in caches.sliceritems) { if (sliceritem.selected) selection = selection + " " + sliceritem.name; } } grab (exception e) { showerror("configuration", (e.innerexception != null) ? e.innerexception.message : e.message); } homecoming selection; }

excel excel-vba pivot-table