Hi All

I was wondering whether someone can help me as I have been struggling with this problem for a while:

|For some reason the attach file button doesn't work as I g=have tried numerous time(it used to be fine before) so I need to explain my problem.

I have a pivot table in a worksheet and have created another sheet which uses lookup to look for the values of my report.

I have a normal Pivot Table (not power Pivot) in sheet Pivot which has the following information:

Week No
Name
Score

I have created another worksheet called Chart.

In chart I have also copied the slicer from my Pivot Table so user can select a multiple weeks and they can view the scores for the chosen weeks.

In Chart:
My data starts from A4 so that a4=1 (week1) , a5=2, a6=3 and so on --This is because the user has chosen weeks 1,2&3 from the slicer on the sheet and Column B show the scores for the relevant week.
in a4 I have the following formula
Please Login or Register  to view this content.
and have copied this down all the way to a52 so as the new weeks appear the a column show the new week number and in column B I have the formula which looks at the week number and returns the relevant sore from the Pivot Sheet. All these ae based on the selection from the slicer. So if user chooses weeks 1-8 a4=1, a5=2 ....a12=8 and a13 will show the greand total and a14:a52 will be blank

I have written a code that automatically imports the data from my data file and creates the pivot table in sheet Pivot.

So if user chooses weeks 5,6,7 from the slicer then a4=5, a5=6 and a6=7 and B4:B6 will show the scores for those weeks.


What I need to do is to write a vba code that creates a Bar chart in Sheet Chart starting from g10 so that the week numbers are on X Axis and y axis have the scores

However What I want to achieve is the the x-axis of the chart changes dynamically as user choose different week numbers , so if user selects weeks 1-8 from the slicer the chart changes the x-axis automatically to reflect what it shows in a4:a.....
as my vlookup formula has iferror to make the cell blank ("") I only want to plot the x-axis with numbers and not Blanks or Grant Total. if W1-6 isselected then x-axis will have 1...6 and y axis will show scores for those weeks and when user chooses 4-8 x-axis will be 4...8 and so on


I really hope this is clear on what I want to achieve.

Any help is appreciate it