Your problem is that your data is not in the proper normalised form for generating Pivot Tables, and you are making life difficult for yourself.
Your data should have just 5 headings, and not grow as you add more periods.
Country, Area, Product, Period, Value
The attached worksheet has a macro that will convert your data into a normalised form, as below
Now with this as the Source data, you can create a Pivot Table which will show Grand Total by Row, as well as by column, so there is no need to sum ranges of values with GetPivotData.
Dim wsS As Worksheet, wsD As Worksheet
Dim lr As Long, lc As Long, i As Long, j As Long, k As Long
Set wsS = Sheets("Data")
Set wsD = Sheets("Data2")
lr = wsS.Cells(Rows.Count, 1).End(xlUp).Row ' number of rows of data
lc = wsS.Cells(1, Columns.Count).End(xlToLeft).Column ' last column populated
wsD.Range("A1") = "Country" ' fill headings on new sheet
wsD.Range("B1") = "Area"
wsD.Range("C1") = "Product"
wsD.Range("D1") = "Period"
wsD.Range("E1") = "Value"
k = 2 ' first row of data to be written on new sheet
For i = 2 To lr ' start from row 2 of source data to the end
For j = 4 To lc ' first three columns are standard, but loop for each column
' from column column 4 to last column
wsD.Cells(k, 1) = wsS.Cells(i, 1)
wsD.Cells(k, 2) = wsS.Cells(i, 2)
wsD.Cells(k, 3) = wsS.Cells(i, 3)
wsD.Cells(k, 4) = wsS.Cells(1, j)
wsD.Cells(k, 5) = wsS.Cells(i, j)
k = k + 1
I think it is also easier to do your Vlookup once, rather than in each formula so I have created a second report area on your sheet Report. The month selected via dropdown is in B12, and in B11 I have the single Vlookup formula to give the column heading from the PT.
Your formulae then simplify to
for each Product for the Month
for the Total for the month
for YTD for each Product
for YTD Total