Sum across columns based on column headers or count of columns

I'm writing a macro using Office 365. I'm a beginning/intermediate vba coder. I only get the opportunity every now and then so I feel my way through.

A piece of the macro needs to be as follows:

Workbook Setup:
I have two sheets in a workbook.
One has a pivot table summing the revenue of customers by month date for 1 year. Columns = months, Rows = Customers
The second sheet is a pivot table in the same format, for different. On the second sheet however, there is only a partial years data. So on the first sheet there is Jan-Dec and on the second, only Jan-Mar. The amount number of months on the second sheet changes so it needs to be versatile depending on when the report is run.

What I need them to do:
I need to create a column on the second sheet, at the end of the pivot table, that sums the same months from the first sheet on each row. So, if the second pivot table has months Jan-Apr, I need to the macro to sum across the totals for only Jan-Apr from the first pivot table.

I have attached a workbook that hopefully will make this a little clearer. Any suggestions or code on how to approach this would be very helpful.

Hi,

While I am sure there is a VBA Guru out there that can figure this out, I think you would be better served with an INDEX MATCH formula.

See that attached modification of your example.

It uses INDEX MATCH MATCH formula's that reference Dynamic Named Arrays to return the requested info.
The benefit of Dynamic Named Arrays is that they will automatically include any new information contained in your reference data. In this case the "Last Year" data.

Let me know if you have any questions.

Hope this is helpful.

Cheers

the index match seems to work, im intrested to see if someone shows a VBA code to see how it works within the pivot tables