×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Variable in Pivot Table

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

RE: Variable in Pivot Table

Arv,

Need to see your worksheet. Dates are tricky & PTs are tricky, especially in PT aggregation headings.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
Hi Skip
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 Source Data is that table that your PT is referencing.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
Hi Skip
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close