fredlo2010
06-23-2012, 06:06 AM
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
Sub ClearFilters()
ActiveWorkbook.RefreshAll
With Sheets("PivotSummary").PivotTables("Store")
.PivotFields("Type").ClearAllFilters
.PivotFields("product").ClearAllFilters
End With
End Sub
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
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").EnableMultiplePageItems _
= True
End Sub
the code I want to get I want to get
ActiveSheet.PivotTables("Store").PivotFields("Type").CurrentPage = "(All)"
With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems("IceCream").Visible = True
End With
ActiveSheet.PivotTables("Stock").PivotFields("Type").EnableMultiplePageItems _
= True
End Sub
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:
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").EnableMultiplePageItems _
= True
End Sub
Thanks a lot
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
Sub ClearFilters()
ActiveWorkbook.RefreshAll
With Sheets("PivotSummary").PivotTables("Store")
.PivotFields("Type").ClearAllFilters
.PivotFields("product").ClearAllFilters
End With
End Sub
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
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").EnableMultiplePageItems _
= True
End Sub
the code I want to get I want to get
ActiveSheet.PivotTables("Store").PivotFields("Type").CurrentPage = "(All)"
With ActiveSheet.PivotTables("Store").PivotFields("Type")
.PivotItems("IceCream").Visible = True
End With
ActiveSheet.PivotTables("Stock").PivotFields("Type").EnableMultiplePageItems _
= True
End Sub
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:
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").EnableMultiplePageItems _
= True
End Sub
Thanks a lot