=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-12,0,12,1)
Not sure how to incorporate that. The recorded macro looks like this:
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Data].[Month].[Month]" _
).VisibleItemsList = Array("[Data].[Month].&[yr2018mth08]", _
"[Data].[Month].&[yr2018mth09]", "[Data].[Month].&[yr2018mth10]", _
"[Data].[Month].&[yr2018mth11]", "[Data].[Month].&[yr2018mth12]", _
"[Data].[Month].&[yr2019mth01]", "[Data].[Month].&[yr2019mth02]", _
"[Data].[Month].&[yr2019mth03]", "[Data].[Month].&[yr2019mth04]", _
"[Data].[Month].&[yr2019mth05]", "[Data].[Month].&[yr2019mth06]", _
"[Data].[Month].&[yr2019mth07]")
Only issue is, this will continually roll or update.
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | Month | year | [Data].[Month]. | |||
2 | 7 | 2019 | 07 | [Data].[Month].&[yr2019mth07] | formula in A3 copied down | =MONTH(EDATE(DATE(B2,A2,1),-1)) |
3 | 6 | 2019 | 06 | [Data].[Month].&[yr2019mth06] | formula in B3 copied down | =YEAR(EDATE(DATE(B2,A2,1),-1)) |
4 | 5 | 2019 | 05 | [Data].[Month].&[yr2019mth05] | formula in C2 copied down | =TEXT(A2,"00") |
5 | 4 | 2019 | 04 | [Data].[Month].&[yr2019mth04] | formula in D2 copied down | =$D$1&"&[yr" & B2 & "mth" & C2 & "]" |
6 | 3 | 2019 | 03 | [Data].[Month].&[yr2019mth03] | ||
7 | 2 | 2019 | 02 | [Data].[Month].&[yr2019mth02] | ||
8 | 1 | 2019 | 01 | [Data].[Month].&[yr2019mth01] | ||
9 | 12 | 2018 | 12 | [Data].[Month].&[yr2018mth12] | ||
10 | 11 | 2018 | 11 | [Data].[Month].&[yr2018mth11] | ||
11 | 10 | 2018 | 10 | [Data].[Month].&[yr2018mth10] | ||
12 | 9 | 2018 | 09 | [Data].[Month].&[yr2018mth09] | ||
13 | 8 | 2018 | 08 | [Data].[Month].&[yr2018mth08] | ||
14 |
Sheet: Strings |
Sub nguerra()
Dim Q As String: Q = Chr(34)
Dim Mth As Long, Yr As Long, r As Long, ws As Worksheet, [COLOR=#ff0000]VSL[/COLOR] As String
Set ws = Sheets("Strings")
[I][COLOR=#006400]'ask user and write to sheet[/COLOR][/I]
Yr = InputBox("Year ?", "Which Year", 2019)
Mth = InputBox("Month", "Which Month", 7)
ws.Range("A2:B2") = Array(Mth, Yr)
[COLOR=#006400][I]'create array string[/I][/COLOR]
VSL = Q & ws.Cells(13, 4) & Q
For r = 12 To 2 Step -1
[COLOR=#ff0000]VSL[/COLOR] = VSL & "," & Q & ws.Cells(r, 4) & Q
Next r
[COLOR=#006400][I]'your code[/I][/COLOR]
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Data].[Month].[Month]").VisibleItemsList = Array([COLOR=#ff0000]VSL[/COLOR])
End Sub
not quite what I'm looking for where intervention is needed by the user