PIVOT - Using a cell outside the table for change the grouping

[Michael.Tarnowski]

Hi experts,

I want to enable users with few knowledge of pivot techniques to
change the grouping of a pivot chart resp. the underlying pivot table.
The idea is, to have a changeable cell value beneath the chart to
enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
table shall change it's groupings accordingly, thus forces to change
the associated chart.

Any idea, how to achieve this?
Thanks and have a nice day
Michael

 

[Shane Devenshire]

Hi,

What kind of groupings are we talking about when we say 2, 4, 6, 5?

What is being grouped and how? What fields, row fields, more than one row
field, column fields, both row and column fields? Are these fields groups on
Month, Day (if so how many), Year, Hour, Minute, Second, Numbers? Or are
these manual groupings? Are these groupings those generated by the Grouping
command or by anding other row or column fields, or by checking and
unchecking filter options?

 

[Michael.Tarnowski]

Hi,

What kind of groupings are we talking about when we say 2, 4, 6, 5?

What is being grouped and how? What fields, row fields, more than one row
field, column fields, both row and column fields? Are these fields groups on
Month, Day (if so how many), Year, Hour, Minute, Second, Numbers? Or are
these manual groupings? Are these groupings those generated by the Grouping
command or by anding other row or column fields, or by checking and
unchecking filter options?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Hi Shane,
thanks for your answer.
It is a manually grouping. Here is a picture:
http://michaeltarnowski.de/temp/Clipboard02.gif

Michael

 

[Michael.Tarnowski]

Hi,

What kind of groupings are we talking about when we say 2, 4, 6, 5?

What is being grouped and how? What fields, row fields, more than one row
field, column fields, both row and column fields? Are these fields groups on
Month, Day (if so how many), Year, Hour, Minute, Second, Numbers? Or are
these manual groupings? Are these groupings those generated by the Grouping
command or by anding other row or column fields, or by checking and
unchecking filter options?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Hi Shane,
thanks for your answer.
It is a manually grouping. Here is a picture:
http://michaeltarnowski.de/temp/Clipboard02.gif

Michael

 

[Shane Devenshire]

Hi,

I see the picture, but I don't see 2, 4, 6, or 5 as option or values
anywhere in the picture. So based on the picture, give me an example of what
you want to have happen. I see the "before" what will it look like "after"?

Also, before I go too much further, keep in mind that the pivot table will
not change just by the user entering a number in a cell. For that to happen
you will need to add VBA code. Second, if the process, whatever it may be,
turns out to be fairly simple, I think you should teach the users how to do
it. One of the problems with programming is that when you leave, if no one
else is a programmer and something goes wrong - nobody has a clue.

 

[Michael.Tarnowski]

Hi,

I see the picture, but I don't see 2, 4, 6, or 5 as option or values
anywhere in the picture. So based on the picture, give me an example of what
you want to have happen. I see the "before" what will it look like "after"?

Also, before I go too much further, keep in mind that the pivot table will
not change just by the user entering a number in a cell. For that to happen
you will need to add VBA code. Second, if the process, whatever it may be,
turns out to be fairly simple, I think you should teach the users how to do
it. One of the problems with programming is that when you leave, if no one
else is a programmer and something goes wrong - nobody has a clue.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Shane,
the grouping happens in the field "weeks open". As you see, "weeks
open" is currently grouped by 5 (manual click in pivot table, then a
right click, "Group and Show Detail>Group").
I'am looking for a VBA code to change this grouping by changing a cell
value outside the pivot table only - thus, no right click in the
table, no more menu selection of "Group and Show Detail>Group", etc.,
-- only entering a value.
Michael

 

[Shane Devenshire]

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Michael.Tarnowski]

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Hi community,
I found a solution:

if worksheet A holds the pivot table, and worksheetB the chart and a
cell names "groups" and C13 is a cell in the pivot table.

Sub PivotGrouping()
' set grouping of pvtDelays table
'
worksheetA.Activate
Range("C13").Select
Selection.Group Start:=True, End:=True, By:=worksheetB.Range
("groups").value
worksheetB.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PivotLayout.PivotTable.RefreshTable
End Sub

Cheers Michael

 

[Michael.Tarnowski]

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Hi community,
I found a solution:

if worksheet A holds the pivot table, and worksheetB the chart and a
cell names "groups" and C13 is a cell in the pivot table.

Sub PivotGrouping()
' set grouping of pvtDelays table
'
worksheetA.Activate
Range("C13").Select
Selection.Group Start:=True, End:=True, By:=worksheetB.Range
("groups").value
worksheetB.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PivotLayout.PivotTable.RefreshTable
End Sub

Cheers Michael

 

[Michael.Tarnowski]

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Hi community,
I found a solution:

if worksheet A holds the pivot table, and worksheetB the chart and a
cell names "groups" and C13 is a cell in the pivot table.

Sub PivotGrouping()
' set grouping of pvtDelays table
'
worksheetA.Activate
Range("C13").Select
Selection.Group Start:=True, End:=True, By:=worksheetB.Range
("groups").value
worksheetB.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PivotLayout.PivotTable.RefreshTable
End Sub

Cheers Michael

 

[Shane Devenshire]

Hi,

I just reread my previous post and see that my code was not sent. Here is
the solution I provided for you

This code allows you to enter a value in H3 and have a pivottable group by
numbers with a By value equal to H3. E4 is the first data cell of the field
you are grouping on.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
On Error GoTo ErrorHandler
Set isect = Application.Intersect(Target, Range("H3"))
If Not isect Is Nothing Then
If Target > 0 Then
Application.EnableEvents = False
Range("E4").Group Start:=True, End:=True, By:=Target
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Michael.Tarnowski]

Hi,

I just reread my previous post and see that my code was not sent. Here is
the solution I provided for you

This code allows you to enter a value in H3 and have a pivottable group by
numbers with a By value equal to H3. E4 is the first data cell of the field
you are grouping on.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
On Error GoTo ErrorHandler
Set isect = Application.Intersect(Target, Range("H3"))
If Not isect Is Nothing Then
If Target > 0 Then
Application.EnableEvents = False
Range("E4").Group Start:=True, End:=True, By:=Target
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Hi Shane,
thank you to share your code with me. I assume both cells H3 and E4
are on the same worksheet. - I was looking for a code to handle H3 on
sheet A and E4 on sheet B and came up with my snippet.
Michael