Dynamically Update a Pivot Table based on Multiple Variables

Good Day All,

long time first time etc.

I am looking to use a DO UNTIL loop to dynamically update a group of Pivot Tables. I have a QTD report (I am going to have to expand this code to work for a YTD report, but first things first) with multiple Pivot Tables. Instead of the user, every month, going to all 7 PTs and updating the month I want VBA to do it for them.

In order to update the report I have a data validation list where the user selects the current month (ie: first week of June, user would select May 2023). Since May is the 2nd month of the quarter, the report will need to update each PT with April and May #s. I have an IF Statement that determines this and lists out the months required in a dynamic named range (ie, as the quarter progresses the range increases until it resets to the next quarter).

So lets continue w/ my May example. For each PT, the format to update is YYYYMM. So in my named range for May 2023 selection I would have 202304 (CQTRVLE01 named range in Excel) and 202305 (CQTRVLE02). What I am trying to figure out is how can I use a DO UNTIL loop do dynamically cycle through the values and update the PT. I have a cell value that determines the current month # in the quarter. I want my loop to recognize that it is the second month and only pull the values for April (202304) and May (202305). I cannot have a blank value because the PT will not update. So even though I have a cell for June, I do not need it until I get to July at which point I will need it.

The variable I am using to assign values to update the PT is CQV0i where i = the month # in the quarter. So since there are 2 months in the quarter, i = 2 and I need 202304 to be CQV01 and 202305 to be CQV02. Furthermore, once the variables have been assigned it needs to update the PT up to the # of months. The PT we use is very basic and it updates based on the values you need only. So for the 2 values I have my PT code would be:

With PT1
     ActiveSheet.PivotTables("BaseCQ").PivotFields( _
        "[Fiscal Period].[Fiscal Year - Month].[Month]").VisibleItemsList = Array( _
        "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV01 & "]", _
        "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV02 & "]", _
        "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV03 & "]")
End With

Each time the month # increases for the quarter, I need to add a line. So if we were in April, I would only need the first line / CQV0i = CQV01. May I would need two lines CQV01 and CQV02. June I would need all 3 CQB01, CQV02, amd CQV03. I cannot update the PT with a blank value, I get an error. So based which month # it is in the quarter, I only need the corresponding # of lines in the code.

This is what I have written so far.

Dim PT1 As PivotTable
Dim x As Long
Dim i As Range
Dim CQV0i As Long

x = 1
Set i = Range("CurrRAngeNum")
Set PT1 = Worksheets("FDW_BaseCY").PivotTables("FDW_BaseCY")

Do Until x = i
Set CQV0i = Range("CQTRVLE0" & i)
I need a line here to update CQV0i and assign each of the months

Pivot Table Code here from PT1 to PT7

x = x + 1
Loop

It wont let me use CQV0i (I get "Object Required"). I've been scouring Stack Online for different ways I can approach this and Frankenstein my code) but I've run up against it here. I know it's a lot and it's pretty complex (for me at least) but I would appreciate any help or guidance. I am going to keep refreshing this page if there are questions but I'm still going to be looking for answers so responses may be delayed. Thank you.

Havent been able to get VBA to acknowledge my dynamic variable. Keep getting Object Required.

I'm a genius

Sub DU()

Dim PT1 As PivotTable
Dim x As Long
Dim i As Range
Dim CQV0x As Long
Dim CQV01 As Variant
Dim CQV02 As Variant


x = 1
Set i = Range("CurrRAngeNum")
Set PT1 = Worksheets("BaseCY").PivotTables("BaseCY")

Do Until x > i

    If i = 1 Then
    CQV0x = Range("CQTRVLE0" & x).Value
    CQV01 = CQV0x
        
    ElseIf i = 2 Then
    CQV0x = Range("CQTRVLE0" & x).Value
    CQV01 = CQV0x
    x = x + 1
    CQV0x = Range("CQTRVLE0" & x).Value
    CQV02 = CQV0x
    
    ElseIf i = 3 Then
    CQV0x = Range("CQTRVLE0" & x).Value
    CQV01 = CQV0x
    x = x + 1
    CQV0x = Range("CQTRVLE0" & x).Value
    CQV02 = CQV0x
    x = x + 1
    CQV0x = Range("CQTRVLE0" & x).Value
    CQV03 = CQV0x
    End If
    
x = x + 1
Loop

    If i = 1 Then
       
        ActiveSheet.PivotTables("BaseCY").PivotFields( _
            "[Fiscal Period].[Fiscal Year - Month].[Month]").VisibleItemsList = Array( _
            "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV01 & "]")
        
    ElseIf i = 2 Then
        
        ActiveSheet.PivotTables("BaseCY").PivotFields( _
            "[Fiscal Period].[Fiscal Year - Month].[Month]").VisibleItemsList = Array( _
            "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV01 & "]", _
            "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV02 & "]")
        
    ElseIf i = 3 Then
        
        ActiveSheet.PivotTables("BaseCY").PivotFields( _
            "[Fiscal Period].[Fiscal Year - Month].[Month]").VisibleItemsList = Array( _
            "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV01 & "]", _
            "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV02 & "]", _
            "[Fiscal Period].[Fiscal Year - Month].[Month].&[1]&[" & CQV03 & "]")
    
    End If


End Sub

Gotta clean it up and add some more PTs but this works. Hope someone finds it useful.