vba - combo box to change filters on excel pivot table -

-

excel 2013. using 3 combo boxes change filters on pivot table. first combo box has "project1", "project2" & all. second combo box has "customer1", "customer2" & all. third combo box has "country1", "country2" & all.

i using 9 pivot tables, of them have filters [project], [customer], [country].

my intention change first combo box project1 & pivot tables filter should change project1.i able that.

however when select first combo box "all". first combo box cell link y1. vba run time error 1004: application-defined or object-defined error.

sub projectname()  activesheet.pivottables("pvt1").pivotfields("project name").clearallfilters activesheet.pivottables("pvt2").pivotfields("project name").clearallfilters activesheet.pivottables("pvt3").pivotfields("project name").clearallfilters      activesheet.pivottables("pvt1").pivotfields("project name").currentpage = range("y1").text     activesheet.pivottables("pvt2").pivotfields("project name").currentpage = range("y1").text     activesheet.pivottables("pvt3").pivotfields("project name").currentpage = range("y1").text

since first 3 lines of code go without issue, assume pivot table pvt1 , field project name exist. places error somewhere after that.

for call .currentpage 1004 error following reasons:

  • using try , filter field not set report filter. cannot use currentpage filter rows or columns
  • setting value not exist in list of possible values

on second point, call range might relevant.

  • verify value there exists in list of possible ones.
  • also aware using .text use display value of cell , not underlying .value

to resolve these issues, there couple of options:

  • for case want filter data on row or column (and not in filters section) can go through pivotitems , set visible = true/false
  • you can set label filter vba if want instead of manual filter
  • if want check value existing in currentpage, can iterate pivotitems pivotfield , check 1 matches. code similar for each loop check on value, don't set visible.

code setting filter on row or column

sub filterpivotfield()      dim pt pivottable     set pt = activesheet.pivottables("pvt1")      dim pf pivotfield     set pf = pt.pivotfields("c")      pf.clearallfilters      'slow iterates items , sets visible (manual filter)     dim pi pivotitem     each pi in pf.pivotitems         pi.visible = (pi.name = range("j2"))     next      'fast way sets label filter     pf.pivotfilters.add2 type:=xlcaptionequals, value1:=range("j2")  end sub

picture of ranges

enter image description here


Comments

Post a Comment

Popular posts from this blog

xslt - Substring before throwing error -

-
i've below xml <?xml version="1.0" encoding="utf-8"?> <body> <p>industrial drawing: creative composition</p> <p>industrial drawing: creative<fn> <fnn>4</fnn> <fnt> <p>ftn1"</p> </fnt> </fn> composition </p> </body> and below xsl. <xsl:template match="p"> <xsl:choose> <xsl:when test="contains(substring-before(./text(),' '),'article')"> <xsl:text>sect3</xsl:text> <xsl:value-of select="./text()"/> </xsl:when> <xsl:when test="contains(substring-before(./b/text(),' '),'section')"> <xsl:text> sect 2</xsl:text> <xsl:value-of select="./text()"/> </xsl:when> <xsl:w...
Read more

oracle - Changing start date for system jobs related to automatic statistics collections in 11g -

-
i have oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production running on windows 2008 r2 somehow got next run dates scheduled jobs set dates in year 2016. jobs created able reset start_date in turn caused next_run_date reset. jobs related oracle automatic statistics collections unable dms_scheduler.set_attribute work changing start_date values. i tried resetting weekly window's dates per oracle document id 1450173.1 using sql following , did in fact reset values see within dba_scheduler_windows had no change dba_scheduler_jobs begin dbms_scheduler.set_attribute ( name => '"sys"."monday_window"', attribute => 'start_date', value => '10-jun-15 10.00.00.000000000 pm us/central'); end; / the job names seeing issue on are: rse$clean_recoverable_script ora$autotask_clean purge_log sm$clean_auto_split_merge bsln_maintain_stats_job mgmt_stats_config_job some of lack schedule names if try dbms_schedul...
Read more

javascript - gulp-nodemon - nodejs restart after file change - Error: listen EADDRINUSE events.js:85 -

-
i'm running nodejs 0.12.3 - gulp 3.10.8 , gulp-nodemon 2.0.3. gulpfile.js : gulp.task('serve', ['build'], function() { gulp.start('fb-flo'); nodemon({ script: paths.server, env: { 'node_env': 'development' }, // env: { 'node_env': 'production' }, execmap: { 'js': 'node_modules/babel/bin/babel-node --stage 1' //es7 cote server }, delay: '0ms', watch: ['src/shared/', 'src/server/'], ignore: ['src/shared/components'], tasks: function (changedfiles) { var tasks = []; changedfiles.foreach(function (file) { if (path.extname(file) === '.jsx' && !~tasks.indexof('bundlejs')) tasks.push('bundlejs'); if (path.extname(file) === '.less' && !~tasks.indexof('compileless')) tasks.push('compileless'); }); return tasks; } ...
Read more