Change PivotTable Field based on Cell Value

[wwebb9]

I'm creating a dashboard that takes old information, moves it to the right, then refreshes the main table with information pulled from pivot tables.

My problem is, the DB is ran every month and thus the PivotTable Field 'Sum of _201301' needs to change to 'Sum of _201302' so the correct information is updated into the table. I'm not really sure the best way to proceed...


My current code for this project is:

Sub Dashboard_CU()


answer = MsgBox("Are you sure you want to run the dashboard?", vbYesNo)
If answer = vbNo Then Exit Sub
     
    Sheets("Dashboard").Range("B14:C28").Copy
    Sheets("Dashboard").Range("G14:H28").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Sheets("Dashboard").Range("B30:C50").Copy
    Sheets("Dashboard").Range("G30:H50").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Sheets("Dashboard").Range("C11").Copy
    Sheets("Dashboard").Range("H11").PasteSpecial xlPasteValues
    Application.CutCopyMode = False


    Dim pt As PivotTable
    Dim wsheet As Worksheet
     
    For Each wsheet In Worksheets
        For Each pt In wsheet.PivotTables: pt.RefreshTable: Next pt
        Next wsheet
    
End Sub