Variable in Pivot Table
Variable in Pivot Table
(OP)
Hi
Seeking help to pass a variable in the below code.
Period = ActiveSheet.Range("A1")
' Range("A1") = Jan 2001
Working
WS.PivotTables(Pvt.Name).PivotFields("[Date].[Year].[Mth]") _
.VisibleItemsList = Array("[Date].[Year].[Mth].&[Jan 2021]")
Not Working
WS.PivotTables(Pvt.Name).PivotFields("[Date].[Year].[Mth]") _
.VisibleItemsList = Array("[Date].[Year].[Mth].&[Period]")
Thanks,
arv
Seeking help to pass a variable in the below code.
Period = ActiveSheet.Range("A1")
' Range("A1") = Jan 2001
Working
WS.PivotTables(Pvt.Name).PivotFields("[Date].[Year].[Mth]") _
.VisibleItemsList = Array("[Date].[Year].[Mth].&[Jan 2021]")
Not Working
WS.PivotTables(Pvt.Name).PivotFields("[Date].[Year].[Mth]") _
.VisibleItemsList = Array("[Date].[Year].[Mth].&[Period]")
Thanks,
arv
Talk To Other Members
RE: Variable in Pivot Table
Need to see your worksheet. Dates are tricky & PTs are tricky, especially in PT aggregation headings.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: Variable in Pivot Table
This is linked to the other issue that you helped me.
I don't have access to the source data and only access to the power pivot.
Not 100% sure how easy it is for me to create some dummy data.
If you can guide where / what i am missing in the code, i can give it a shot.
Else, I will see if i can create some dummy data.
Thanks,
arv
RE: Variable in Pivot Table
The workbook that you previously provided had no dates in the Source Data table.
So I'm saying that if you have added dates to your Source Data and have a PT that uses those dates, I'd like to see that workbook to help you to a solution.
In your previous thread, you assumed a METHOD and focused your efforts on that METHOD: a PivotTable. You have persisted in keeping the focus on METHOD rather than stating a functional description of your problem and discovering what METHOD various members suggest.
For instance, I was puzzled by your Items Array and it was only when I SAW the data and expressed the inconsistencies between that and the actual data in your Source Data, and you expressed your intent with inserting invalid data, that I immediately envisioned a solution because I had access to your Source Data and saw your attempt in your PT.
Need to see your current workbook!
But I still have no idea of what your attempting to accomplish with this workbook. Just a few peeks of your PT method, which up to this point, seems pointless IMNSHO.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: Variable in Pivot Table
Thanks for taking the time to look into my problem.
I managed to work it out with the ampersand.
WS.PivotTables(Pvt.Name).PivotFields("[Date].[Year].[Mth]") _
.VisibleItemsList = Array("[Date].[Year].[Mth].&[" & Period & "]")
I totally get what you need and I do apologize for not being able to provide the full source data.
Normally, i would have been able to create dummy data but given the Pivot Table is created from data models and i dont have access to those, makes it a bit difficult.
Thanks,
arv