Macro not working

dgavilanes · Aug 11, 2012

Hi All,

I created a macro but it does not work, attached is the code.

What is supposed to do from a master dropdown box is syncronized the other drown down boxes, what it cannot do is deselect the previous selection and select a new value.

the eror message reads as follows. run-time error 1004

application-defined or object-defined error, please help!!!

Sub mastpvt()

'

' mastpvt Macro

'

'

ActiveSheet.PivotTables("pvt1").PivotFields("NICKNAME").CurrentPage = Range("E1").Text

With ActiveSheet.PivotTables("pvt1").PivotFields("NICKNAME")

.PivotItems("MAY").Visible = True

.PivotItems("ADDAMS").Visible = False

End With

ActiveSheet.PivotTables("pvt2").PivotFields("NICKNAME").CurrentPage = Range("E1").Text

With ActiveSheet.PivotTables("pvt2").PivotFields("NICKNAME")

.PivotItems("MAY").Visible = True

.PivotItems("ADDAMS").Visible = False

End With

ActiveSheet.PivotTables("pvt3").PivotFields("NICKNAME").CurrentPage = Range("E1").Text

With ActiveSheet.PivotTables("pvt3").PivotFields("NICKNAME")

.PivotItems("MAY").Visible = True

.PivotItems("ADDAMS").Visible = False

End With

ActiveSheet.PivotTables("pvt4").PivotFields("NICKNAME").CurrentPage = Range("E1").Text

With ActiveSheet.PivotTables("pvt4").PivotFields("NICKNAME")

.PivotItems("MAY").Visible = True

.PivotItems("ADDAMS").Visible = False

End With

Hui · Aug 11, 2012

Dgavilanes

I would add a single line after each PT update

[pre]

Code:

&#39;Application.CutCopyMode = False
like:

Sub mastpvt()
&#39;
&#39; mastpvt Macro
&#39;

ActiveSheet.PivotTables(&#34;pvt1&#34;).PivotFields(&#34;NICKNAME&#34;).CurrentPage = Range(&#34;E1&#34;).Text
With ActiveSheet.PivotTables(&#34;pvt1&#34;).PivotFields(&#34;NICKNAME&#34;)
.PivotItems(&#34;MAY&#34;).Visible = True
.PivotItems(&#34;ADDAMS&#34;).Visible = False
End With
Application.CutCopyMode = False

ActiveSheet.PivotTables(&#34;pvt2&#34;).PivotFields(&#34;NICKNAME&#34;).CurrentPage = Range(&#34;E1&#34;).Text
With ActiveSheet.PivotTables(&#34;pvt2&#34;).PivotFields(&#34;NICKNAME&#34;)
.PivotItems(&#34;MAY&#34;).Visible = True
.PivotItems(&#34;ADDAMS&#34;).Visible = False
End With
Application.CutCopyMode = False

ActiveSheet.PivotTables(&#34;pvt3&#34;).PivotFields(&#34;NICKNAME&#34;).CurrentPage = Range(&#34;E1&#34;).Text
With ActiveSheet.PivotTables(&#34;pvt3&#34;).PivotFields(&#34;NICKNAME&#34;)
.PivotItems(&#34;MAY&#34;).Visible = True
.PivotItems(&#34;ADDAMS&#34;).Visible = False
End With
Application.CutCopyMode = False

ActiveSheet.PivotTables(&#34;pvt4&#34;).PivotFields(&#34;NICKNAME&#34;).CurrentPage = Range(&#34;E1&#34;).Text
With ActiveSheet.PivotTables(&#34;pvt4&#34;).PivotFields(&#34;NICKNAME&#34;)
.PivotItems(&#34;MAY&#34;).Visible = True
.PivotItems(&#34;ADDAMS&#34;).Visible = False
End With
Application.CutCopyMode = False

[/pre]

dgavilanes · Aug 11, 2012

Thanks for replying, I still get the same error

dgavilanes · Aug 11, 2012

I'm including a link

http://sdrv.ms/TqlCBn

Thanks

Dennis

Hui · Aug 11, 2012

Dennis

I think this will do it for you

Before running this select each Page Field Nickname and change it so that Select Muliple Items ISN'T ticked

[pre]

Code:

Sub mastpvt()
&#39;
&#39; mastpvt Macro
&#39;
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = True

PgField = Range(&#34;G1&#34;)
Sheets(&#34;Master&#34;).PivotTables(&#34;pvt1&#34;).PivotFields(&#34;Nickname&#34;).CurrentPage = PgField
Sheets(&#34;Master&#34;).PivotTables(&#34;pvt2&#34;).PivotFields(&#34;Nickname&#34;).CurrentPage = PgField
Sheets(&#34;Master&#34;).PivotTables(&#34;pvt3&#34;).PivotFields(&#34;Nickname&#34;).CurrentPage = PgField
Sheets(&#34;Master&#34;).PivotTables(&#34;pvt4&#34;).PivotFields(&#34;Nickname&#34;).CurrentPage = PgField

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[/pre]

dgavilanes · Aug 11, 2012

Hello Hui,

I try it but I 'am still getting the same error message. Sorry to be a pest

Dennis

Hui · Aug 11, 2012

Did you select each Pivot Table, Page Field "Nickname" and change it so that Select Muliple Items ISN'T ticked?

Here is your file: https://www.dropbox.com/s/w766r5hshxam96d/test%20template_demo_isat.xlsm

When you say it isn't working, please elaborate?

Also what version of Excel are you using ?

Debraj · Aug 11, 2012

Hi Hui,

Just for curiosity..

* The code provided in post#36792 and file provided in post#36814, both are same.. except variable name (previously PgField and now SPgField2)..

Then why previous code got error and second was working..

I hope PgField is not even a Keyword..

Thanks..

Deb

EDIT:

Sorry IAN, I got it..

First File is with ....EnableMultipleItem = True

and Second File is with ....EnableMultipleItem = False

Hui · Aug 11, 2012

Both should be

EnableMultipleItem = False

I just cleaned up the code in the post

Does it work for you Deb ?

Debraj · Aug 11, 2012

Ian,

Yes Its working perfectly..

One silly Question..

How can I set value for SPgField2 by

Code:

SPgField2 = Application.Caller.???

Function..

Regards,

Deb

dgavilanes · Aug 11, 2012

Hui,

I do not know what to say other than ""THANK YOU GUYS"" !!! you guys are awesome.

if you ever come this way (Chicago)I show you this great city, you have a friend her

regards, Dennis

Macro not working

dgavilanes

Member

Hui

Excel Ninja

dgavilanes

Member

dgavilanes

Member

Hui

Excel Ninja

dgavilanes

Member

Hui

Excel Ninja

Debraj

Excel Ninja

Hui

Excel Ninja

Debraj

Excel Ninja

dgavilanes

Member