Can I make a switch button which can exchange the column and row in pivot table?

[#1]

Hello

Like the pic shows, Table 1 and table 2 are pivot tables and have the same content.

I want a button to switch table1 and table2. How can I do ?  Does a  micro do this?

I think there must be a property which can control the tables.

Who can help me about this? Thank you very much.

[m_woolf]

You could make 2 pivottables and use a button to determine which is visible.

[#3]

you can do it by macro

see att file

[#4]

Hi mwooif，

Thanks for your reply.

Because of my dimensions and expressions are all dynamic. My opinion trended towards Pari Pari‘s even I don not understand his opinion totally.

[#5]

Hi Pari Pari,

From your test qvw 's macro I find the difference between sub1 and sub2 is the sequence

chart.AddDimension "week"
chart.AddDimension "year"
 

But I can't understand why the difference could affect the table’s stucture.

So could you please explain me more in details?

Thank you

[#6]

I get it!

The key is cp.TableProperties.NumberOfLeftDimensions = 0

[#7]

HI Pari,

One more question:

I wanna switch between 2 tables below. Let the macro control the table's look like.

I look up the API Guide but no clue. Please help me again. Thanks .

[#8]

i can't see the difference between your tables...

[#9]

The second column label.  One is "sum(value)"   the other is blank.

I can drag the label to switch with mouse.  

But I want a macro to control that.

[#10]

it is a something like bug

why you want to achieve that?

i suggest to use (show/hide) label name for this expression in your macro script

see att qvw file