0

So I've tried in vain to get the instructions from this post to work with my data. I keep getting a run-time error 1004. My goal is to input a company name into cell I1 of the same sheet as the pivot table and then have the pivot table filter by that entry. Here's my adapted code:

Sub filtercompany()
    ' Filtercompany
    Dim FilterValue As String
    FilterValue = ActiveSheet.Range("I1").Value 'get value for filter

    ActiveSheet.PivotTables("PivotTable1") _
        .PivotFields("[Company].[Name].[Name]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Company].[Name].[Name]") _
        .CurrentPageName = "[Company].[Name].&[" & FilterValue & "]"
End Sub
Community
  • 1
  • 1
mylegs
  • 1
  • 1
  • 3
  • Have you tried hardcoding the string you pass on the `CurrentPageName` to just see if it works? – L42 Jan 14 '15 at 03:04
  • So, no. The following isn't working for company named AAA. Sub filtercompany() ' Filtercompany ActiveSheet.PivotTables("PivotTable1") _ .PivotFields("[Company].[Name].[Name]").ClearAllFilters ActiveSheet.PivotTables("PivotTable1").PivotFields("[Company].[Name].[Name]") _ .CurrentPageName = "[Company].[Name].&[AAA]" End Sub Is there an actual way to do this using any type of VBA? I've tried recording a macro, but that isn't working either. Is it me or powerpivot? Or both? – mylegs Jan 14 '15 at 15:48
  • Do you actually have that PivotField name? I mean, that string is not a standard format. You should be using the actual PivotField name you got in your PowerPivot. – L42 Jan 15 '15 at 00:39
  • Yes, those are the names of the fields and table in the PowerPivot data model. I can make this work in a regular pivot table, but PowerPivot is giving me heartburn... – mylegs Jan 15 '15 at 16:28

1 Answers1

0

Check out my string. It Works fine when I gave a projektnumber to i

Dim i As Variant

i = 216722

ActiveSheet.PivotTables("PiLEverantör").PivotFields( _
    "[Tabell2].[Kolumn1].[Kolumn1]").VisibleItemsList = Array( _
    "[Tabell2].[Kolumn1].&[" & i & "]")
End Sub
Pierre
  • 1
  • I was able to make this work for PowerPivot using the method described by Pierre above, but only once I checked "Select Multiple Items" within the pivot table. – Matt Garrison May 06 '16 at 20:58