Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110557

Re: Pivot Table run-time error 1004 (PivotField class)

Show all headers | View raw

> Hi Garry,
>
> It's been a while since I've posted, and it's always a pleasure to
> hear from you.  I plugged this in, and it does indeed run; however,
> unfortunately, it's still about as slow as the other other code.  I
> think it's because it's actually loading the pivot table with every
> iteration of the loop.  And there are over 300 pivot items to load.  
>
> When I actually go to the pivot table and select a catalog number from
> the slicer, it loads in just a couple seconds.  Is there a reason I'm
> not able to simply clear the filters and load the catalog number that
> gets put into the variable from the double-click event?  
>
> Thanks Garry!
>
> Frank
>
> On Fri, 06 Apr 2018 07:35:45 -0400, GS <gs@v.invalid> wrote:
>
>> Oops.., forgot to refresh...
>> 
>> Dim sCatNum$, vItem
>> If (Target.Column = 4) Then
>>  'Cancel EditMode and get Catalog Number
>>  sCatNum = Target.Value: Cancel = True
>>  With Sheets("Slicers (all)").PivotTables("PivotSlicer")
>> 	With .PivotFields("Catalog #")
>> 	  .ClearAllFilters
>> 	  Application.EnableEvents = False
>> 	  For Each vItem In .PivotItems
>> 	    vItem.Visible = (vItem = sCatNum)
>> 	  Next 'vItem
>> 	  Application.EnableEvents = True
>> 	End With '.PivotFields("Catalog #")
>> 	.RefreshTable
>>  End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
>> End If '(Target.Column = 4)

The pivot table functions are written in C++ which is *orders of magnitude* 
faster than VB. I was hoping the link I posted would contain some way to 
automate the slicer. Optionally, turn on the macro recorder and see what code 
(if any) it generates when you do it manually.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-05 20:14 -0400
  Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 06:55 -0400
  Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 07:23 -0400
    Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 07:35 -0400
      Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-06 20:13 -0400
        Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 20:42 -0400
          Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-07 13:09 -0400
            Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-07 15:33 -0400
              Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-07 15:52 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-08 14:02 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-08 16:08 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-09 06:34 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-09 16:29 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-09 22:01 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-09 23:10 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-10 14:05 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-10 22:10 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-11 00:03 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-11 05:30 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-11 10:46 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-14 09:09 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-14 13:58 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-17 18:15 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-17 19:48 -0400

csiph-web