Solved: Pivot Table Source and Filter

[fredlo2010]

Hello guys,

I am trying to create a pivot table to get a summary of my data.

1. My first question is is there a way to dynamically change and populate my data. My pivot table pulls values from Sheet("Sheet1") and range "A1:C10" but tomorrow it might need to get it from "A1:A20".
Is there a way to do this dynamically?

My work around this was to set my data source as Sheet1!$A$1:$E$10000 and then use this refresh before my code

[VBA]Sub ClearFilters()

ActiveWorkbook.RefreshAll

With Sheets("PivotSummary").PivotTables("Store")

.PivotFields("Type").ClearAllFilters
.PivotFields("product").ClearAllFilters

End With
End Sub
[/VBA]

2. Then I need to filter the data by "Type" and copy the result into a different sheet.( the copy part is not here yet)

I recorded this code but the issue is that it will get very hard to follow because I will have to set to false all the items I do not want to see and I want to set to true what I want to see. I have played around with this for a little bit now but I cannot figure it out.

My recorded code

[VBA]Sub Macro3()
'
' Macro3 Macro
'

ActiveSheet.PivotTables("Store").PivotFields("Type").CurrentPage = "(All)"
With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems("Cakes").Visible = False
.PivotItems("Cupcakes").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("Stock").PivotFields("Store").EnableMultiplePageIte ms _
= True
End Sub[/VBA]

the code I want to get I want to get

[VBA]ActiveSheet.PivotTables("Store").PivotFields("Type").CurrentPage = "(All)"
With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems("IceCream").Visible = True
End With
ActiveSheet.PivotTables("Stock").PivotFields("Type").EnableMultiplePageItem s _
= True
End Sub[/VBA]

By the way the ultimate goal is to turn this into a procedure that I can use multiple times and feed the values to a variable

Like this:

[VBA]

sub populate(varType as string)

ActiveSheet.PivotTables("Store").PivotFields("Type").CurrentPage = "(All)"
With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems(varType).Visible = True
End With
ActiveSheet.PivotTables("Stock").PivotFields("Type").EnableMultiplePageItem s _
= True
End Sub[/VBA]

Thanks a lot

[Tinbendr]

As far as your reference, look up Dynamic named ranges.

[fredlo2010]

Thanks a lot Timbendr,

I am so happy I did not know such thing existed. I will be applying to not only my pivot Tables but a whole bunch of stuff I have using ranges larger than they should be. This will even speed up my vlookups and calculations.

I just want to add that when you want to specify the source for the pivot table with a Dynamic range first you will have to name the dynamic range and they the source will be the name of the range.

http://www.contextures.com/xlPivot01.html

[Tinbendr]

I am so happy I did not know such thing existed.

The only annoying thing about DNR is that they won't show up in the namebox. But, of course, they are in the Name Manager.

But the good outweighs the bad.

[fredlo2010]

Truth is , with a range that covers thousands of rows and several columns, who needs the name showing in the namebox?

Do you have feedback on the filters?

Here is the whole issue. Maybe there is another way around it and not the pivot table. I have this huge stock list that for some reason has two different codes. Actually the same product has different codes depending on the region. So I need to copy the raw codes into a different auxiliary sheet and there a formula is going to fix the codes:

So if I had to codes, 1111FL and 1111AZ for my cookies now I will end up with a code 1111.

Now is when I use a pivot table to add up the quantities and get a general stock. The data then will be filtered by zone and copy-pasted into other sheets I have created.

Thanks

[Tinbendr]

I would split the stock list into code and region, dump it into Access, then write queries to return what I needed using a userform with dropdown. But it's difficult to offer suggestions as I cannot see the results you need. It may take a little trial and error to 'get the data' how you want it.

You might get away with just autofilter if all you're doing is filtering by region with maybe a subtotal.

My experience with pivot tables is really limited, so someone might offer a better suggestion.

[fredlo2010]

Maybe this will sound horrible but I am "Access illiterate" I will try to create a dummy spreadsheet to show with more clarity what I want.

A Also I am sure the other guys in the forum will help as well. Its kinda frustrating because there is only one line that does not work.

[VBA]With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems("IceCream").Visible = True
End With[/VBA]

[fredlo2010]

Hello guys,

I finally solved the issue with the filter. It was a little bit easier than I thought. Intead of turning the items I want off and on. I had to change the .CurrentPage to the name of my field. I found the help in this forum

so my code went from:

[VBA]With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems("IceCream").Visible = True
End With[/VBA]

to:

[VBA]ActiveSheet.PivotTables("Store").PivotFields("Type").CurrentPage = "Ice Cream"[/VBA]

Thanks a lot