Revised Report and still more issues on the Calculations (Tweaking the codes)

Hi all,
Sorry for being back so soon but there has been some changes on my requirement and I am struggling in tweaking the codes I was given the last time.
Hope you could help me once again, I'm very very desperate ;(
Just a bit of a background, I am creating a report that will allow me to display data from my other sheet based on the selection on my dropdown menus.
I was helped by another forumer on this by doing filters with Macro, this part is working so far.

My issues remains with the Calculations.
My Goal here is to compare figures/sum values for the last 3 Years.
On my dropdown, I have a Selection for Period.

What I need my data to display is If I select a Month, it will display the results in Columns G,H and I.

The Calculations should be like this.
If I select the Month (for example I selected January)

On Column G for Year1
I will get the Sum total of
FEB 2014,MAR 2014,APR 2014,MAY 2014,JUN 2014,JUL 2014,AUG 2014,SEP 2014,OCT 2014,NOV 2014,DEC 2014,JAN 2015 for the corresponding row


On Column H for Year2
I will get the Sum total of
FEB 2015,MAR 2015,APR 2015,MAY 2015,JUN 2015,JUL 2015,AUG 2015,SEP 2015,OCT 2015,NOV 2015,DEC 2015,JAN 2016 for the corresponding row

On Column I for Year3
I will get the Sum total of
FEB 2016,MAR 2016,APR 2016,MAY 2016,JUN 2016,JUL 2016,AUG 2016,SEP 2016,OCT 2016,NOV 2016,DEC 2016,JAN 2017 for the corresponding row


If I select another Month for Example February, my Calculations should start from March like below.
MAR 2014,APR 2014,MAY 2014,JUN 2014,JUL 2014,AUG 2014,SEP 2014,OCT 2014,NOV 2014,DEC 2014,JAN 2015, FEB2016 for the corresponding row

And so on...

Another forumer helped me and it is already working okay but now my requirements have changed.
Originally, I only needed Year1 and Year2. Now I need Year 3.

The References for the codes are hidden further down the right side of the "Dashboard" Sheet starting from column ZQ.
I have attached my working XLSM sheet and I hope you could find time helping me as I'm really really desperate to get this thing working.

Thank you so much in advance for all the help.
-Andrew

Hi again!
You already done most of changes, nice job.
You should try to debug your code, go through it step-by-step, look at variables ion a different code phases.
I've made some corrections in arrays dimentions and loop borders, seems it shows some numbers now.
Please, test it and compare with your raw data.

Hi Kasan, thank you for replying. I have been scratching my head all day to be honest.
I tried your revised sheet, after clearing all the filters except for the month I am getting Run-Time Error 13, Type Mismatch on this line:

If arr(x, 4) = ws1.Range("ZZ9") Or ws1.Range("ZZ9") = "" Then

Any ideas?

Yap, got it too for Japan.
Check out your data, Sheet "Source" rows 996, 1044 and some other contains "#N/A".
This error loads into array and when we try to compare it to some value - we get error.
I've cleaned those cells with "#N/A" and it's working again.

You're a genius! Thank you so much, I will continue checking and report back.
I will keep this thread open for awhile (I hope the mods don't mind).

Thank you so much again dear sir for all your help.

Hi Kashan,
I am back again. I wanted to ask something about a part on the code below:

Please Login or Register  to view this content.

38 is the number of columns on the period. This would mean that each time I will add a new month on my worksheet, I would also need to increase this value on the code.
Is it possible to modify the code so that it will be smart enough to determine the number of columns rather than manually coding it each month?
My Data Source will be updated each month.
Thank you again and looking forward to your advise.

Cheers,
Andrew

Hi,
You are right, this part is "hardcoded". To make it automatically - you can count how many columns are there in row #4 on a sheet Source, like

Please Login or Register  to view this content.

then use value of this variable as limit for the loop.
BUT you should review all code, because this part use static ranges of dates too:

Please Login or Register  to view this content.

You can count how many columns are in row #13 and replace Range() references with Cells() references.