# Dynamic Chart with data from continuous table

I'd like to create a dynamic chart which shows all orders by months. The user enters the start year and the end year. He or she can also select the orders by shop or all shops (value  0).  I use the following formula for counting the orders in C5:

``````=IF(\$A\$2=0;SUMPRODUCT((YEAR(Orders[Date])=A5)*(MONTH(Orders[Date])=C5));SUMPRODUCT((YEAR(Orders[Date])=A5)*(MONTH(Orders[Date])=C5)*(Orders[Shop]=\$A\$2)))
``````

The chart in my example workbook is static as I don't know how to make them dynamic. I need to use VBA to set the start year/end year and and fill the remaining rows with the correct year/month and the formula. I don't know how to do that. Everything is controlled with userforms and the chart will be copied in a userform. Can this also be done with a Pivot Chart? I'm not good with such charts though ..

Could you please have a quick look at my workbook and give me a clue how this can be done?

Thanks

Massimo

Example.xlsm
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft MVP ExcelCommented:
Hello,

you've made a good start with the Pivot Table. Group the date by month and year (right-click any one of the dates in the pivot table, then select "Group". In the next box, select "month" and "Year" and click OK).
Next, drag the Shop field into the Values box and set it to Count (not Sum).

Now create a Pivot Chart (click a cell in the Pivot Table, then click the Pivot Table Tools ribbon > Options > Pivot Chart.

Finally, click the Pivot table again and on the Pivot Options ribbon click "Insert Slicer".

Tick the boxes for "Years" and "Shop". Arrange the boxes and the chart.

Now you can select one (or more) years and one (or more) shops to be plotted in the chart.

See attached.

cheers, teylyn
27836151.xlsm

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

InternshipAuthor Commented:
hi thanks a lot
As the data in the table is set with userforms, I have to check how to set the values with VBA. I obviously need to know how to select all shops or only one .. the years etc.
I will change the values myself with the macro recorder running. That will give me a clue how to control a pivot table with VBA.
I will get back to this thread asap
massimo
InternshipAuthor Commented:
Hi

Well, I had a look at the code the macro recorder recorded and tried to amend it. PT are new to me, so my code doesn't quite work. For example: Why does .PivotItems.Count not count the correct number of items?

``````Option Explicit

Dim wbk As Workbook
Dim ws As Worksheet

Sub ShowShop(i As String)

'Set workbook variables
Set wbk = ThisWorkbook
Set ws = wbk.Worksheets("Pivot")

'PT Variable
Dim pt As PivotTable
Dim pi As PivotItem

'Set PT Name
Set pt = Tabelle3.PivotTables("PivotTable1")

'How many shops are in the field Shop?
countShops = pt.PivotFields("Shop").PivotItems.Count '<--- not correct, why?

'Speed Up
pt.ManualUpdate = True

'If passed argument is 0, then display all shops
If ShopID = 0 Then
ws.PivotTables("PivotTable1").PivotFields("Shop").ClearAllFilters

Else

'Otherwise deselect all items, and set the passed argument to be visible
For Each pi In pt.PivotFields("Shop").PivotItems
Select Case pi.Value + 0
Case 1 To countShops
pi.Visible = False
With ws.PivotTables("PivotTable1").PivotFields("Shop")
.PivotItems(i).Visible = True
End With
Case Else

End Select
Next pi
End If

pt.ManualUpdate = False

End Sub
``````

The next step would be to add arguments for the years. How would you do that?
27836151.xlsm
Microsoft MVP ExcelCommented:
Sorry, I don't do that kind of VBA,

But then again, you don't need VBA for that. Use Slicers instead of user forms. Users can select the slicers and the pivot table and chart will update.
InternshipAuthor Commented:
OK thanks for your help. You did help me. I know that I don't need VBA to do that but I have been asked to do it that way. :-( Maybe someone else can help me further..  I've updated my workbook. I now know how to count the items ..but don't know how to change the years. Here is my code, screengrab and workbook.

``````Option Explicit

Dim wbk As Workbook
Dim ws As Worksheet
Dim iCountShops As Integer

Sub ShowShop(i As String)
Application.ScreenUpdating = False

Set wbk = ThisWorkbook
Set ws = wbk.Worksheets("Pivot")

Dim pt As PivotTable
Dim pi As PivotItem

'PT name
Set pt = ws.PivotTables("PivotTable1")

'Count the number of shops
iCountShops = WorksheetFunction.Count(Range("Shops"))

'Speed Up
pt.ManualUpdate = True
pt.PivotCache.Refresh

'If 0, display all Shops.
If i = 0 Then
ws.PivotTables("PivotTable1").PivotFields("Shop").ClearAllFilters

' Now I need to filter the years.
' Clear all filters
ws.PivotTables("PivotTable1").PivotFields("Year").ClearAllFilters
' Now I need to set the selected years - don't know how to.
' I assume that I need to loop through the years the same way
' I looped through all the shops.

'Otherwise display selected shop only. Remove all filters first.
Else

ws.PivotTables("PivotTable1").PivotFields("Shop").ClearAllFilters
For Each pi In pt.PivotFields("Shop").PivotItems

Select Case pi.Value + 0
'Set the visibility of each Item to False
Case 1 To iCountShops
pi.Visible = False
'Set the selected shop to Visible
With ws.PivotTables("PivotTable1").PivotFields("Shop")
.PivotItems(i).Visible = True
End With

' Now I need to filter the years.
' Clear all filters
ws.PivotTables("PivotTable1").PivotFields("Year").ClearAllFilters
' Now I need to set the selected years - don't know how to.
' I assume that I need to loop through the years the same way
' I looped through all the shops.

Case Else

End Select
Next pi
End If

pt.ManualUpdate = False
Application.ScreenUpdating = True

End Sub
``````

PivotVBA.xlsm
Microsoft MVP ExcelCommented:
Maybe the person who has asked you to do this in VBA, instead of using the out of the box tools that require just a few clicks, needs a little demo on what is now possible with Excel.

As Excel experts and providers of end user solutions we sometimes need to question the requirements and clauses. Very often requirements are based on wrong/old knowledge. They may not know that things are now a lot easier. They may ask for VBA because they cannot imagine that there may be something different.

Be bold. Why don't you show the person who has asked for a VBA solution how it can be done without VBA? Out of the box pivot and a few clicks adding slicers will be much cheaper and more easy to maintain than custom code.

But, if you prefer to re-invent the wheel, go for it!
InternshipAuthor Commented:
Hello Teylyn.

I demonstrated my boss the features and advantages of a Pivot Table and she was impressed. She decided to use Pivot Tables in worksheets instead of userforms.