Pivot Table Report Filter matching

[Mikeykt]

Hi

I'm trying to connect my Pivot table report filter to a corresponding combo box on a front page.

There are five boxes and report filters with matching names, I've tried the following code but I keep getting 'Object doesn't support this property'

Can anyome help?

Sub pivotchoice()
'
' pivotchoice Macro
'
Worksheets("GIFTS").Select
ActiveSheet.PivotTables("Gifts").PivotFields("Campaign").CurrentPage = Worksheets("FrontSheet").CampaignNameGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Campaign_Code").CurrentPage = Worksheets("FrontSheet").CampaignNumGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Campaign_Year").CurrentPage = Worksheets("FrontSheet").CampaignYearGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Can_Mail").CurrentPage = Worksheets("FrontSheet").CanMailGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Can_Phone").CurrentPage = Worksheets("FrontSheet").CanPhoneGCombo.Value
End Sub

 

[Mikeykt]

Hi

Sorry for taken up so much of your time, but it's not recognising the dropdown now.

Can you post the entire vba code I write, so that i can cross check where I've made the mistake?

Thanks for your help

 

[Andrew Poulsom]

Didn't my last piece of code work for you?

 

[Mikeykt]

No, I'm getting runtime errors 9, which is an array problem, which is why i think the problem is with my code and not yours.

 

[Andrew Poulsom]

If you are getting a 'subscript out of range' error it means you don't have a ComboBox from the Forms Toolbar with that name. If you right click it what do you see in the Name Box to the left of the Formula Bar?

 

[Mikeykt]

It's 'CampaignNameG', I renamed it when I created it to reference back to it before writing the macro.

 

[Andrew Poulsom]

So does this work?

Sub Test()
    With Worksheets("FrontSheet").DropDowns("CampaignNameG")
        MsgBox .List(.ListIndex)
    End With
End Sub

 

[Mikeykt]

Hi

I think we're on the right track, the only thing is that now it brings up the value listed in the combo box as a seperate Action window

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 101.25pt; HEIGHT: 80.25pt" id=_x0000_i1025 type="#_x0000_t75"><v:imagedata o:title="" src="file:///C:\DOCUME~1\MICHAE~1.BAR\LOCALS~1\Temp\msohtml1\02\clip_image001.png"></v:imagedata></v:shape></v:shapetype>

Can I just check with you that I've copied your code in the right place....find my pivot vba below:

ActiveWorkbook.Worksheets("MailingPivot").PivotTables("PivotTable2").PivotCache _
.CreatePivotTable TableDestination:="GiftPivot!R1C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("GiftPivot").Select
Cells(1, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields( _
"NAME_KEY " _
), _
"Count of NAME_KEY " _
, xlCount

With Worksheets("FrontSheet").DropDowns("CampaignNameG")
MsgBox .List(.ListIndex)
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"CAMPAIGN_NUMBER_RESPONDED_TO " _
)
.Orientation = xlPageField
.Position = 1
End With

 

[Andrew Poulsom]

Try this version of your original code:

Sub pivotchoice()
'
' pivotchoice Macro
'
    Worksheets("GIFTS").Select
    With Worksheets("FrontSheet")
        With .DropDowns("CampaignNameG")
            ActiveSheet.PivotTables("Gifts").PivotFields("Campaign").CurrentPage = .List(.ListIndex)
        End With
        With .DropDowns("CampaignNumG")
            ActiveSheet.PivotTables("Gifts").PivotFields("Campaign_Code").CurrentPage = .List(.ListIndex)
        End With
        With .DropDowns("CampaignYearG")
            ActiveSheet.PivotTables("Gifts").PivotFields("Campaign_Year").CurrentPage = .List(.ListIndex)
        End With
        With .DropDowns("CanMailG")
            ActiveSheet.PivotTables("Gifts").PivotFields("Can_Mail").CurrentPage = .List(.ListIndex)
        End With
        With .DropDowns("CanPhoneG")
            ActiveSheet.PivotTables("Gifts").PivotFields("Can_Phone").CurrentPage = .List(.ListIndex)
        End With
    End With
End Sub

 

[Mikeykt]

That works perfectly.

Thank you very much.

You have been fantastic!!!!