Task: Change the time in the pivot table according to the latest update of the table. Example: Last update was at 3:00 pm, 15 is displayed in cell J2 (the ЧАС() function). You should get something like this macro record:

 ThisWorkbook.Sheets("Stat Today").PivotTables("СводнаяТаблица13").PivotFields( _ "[Time HH].[Time by Periods].[Period]").VisibleItemsList = Array( _ "[Time HH].[Time by Periods].[Period].&[Night]") ThisWorkbook.Sheets("Stat Today").PivotTables("СводнаяТаблица13").PivotFields( _ "[Time HH].[Time by Periods].[H]").VisibleItemsList = Array( _ "[Time HH].[Time by Periods].[H].&[9]", "[Time HH].[Time by Periods].[H].&[10]", _ "[Time HH].[Time by Periods].[H].&[11]", "[Time HH].[Time by Periods].[H].&[12]" _ , "[Time HH].[Time by Periods].[H].&[13]", _ "[Time HH].[Time by Periods].[H].&[14]", "[Time HH].[Time by Periods].[H].&[15]" _ )

My code does not work, although it does not issue errors

 Dim N As Integer Dim M As Integer N = ThisWorkbook.Sheets("Stat Today(MainSources)").Cells(2, 10).Value M = 8 Do Until M > N ThisWorkbook.Sheets("Stat Today").PivotFields( _ "[Time HH].[Time by Periods].[Period]").VisibleItemsList = Array( _ "[Time HH].[Time by Periods].[Period].&[Night]") ThisWorkbook.Sheets("Stat Today").PivotFields( _ "[Time HH].[Time by Periods].[H]").VisibleItemsList = Array( _ "[Time HH].[Time by Periods].[H].&[M]") M = M + 1 Loop
  • What is M = 8 ? If N = 15 , then the condition M > N will not be executed, respectively, the code works, but does nothing but assignment - slippyk
  • correctly understand that we need all time periods from 8:00 to 15:00? - slippyk
  • @slippyk 1.count should start from 8 (since from 1-8 it is selected from [Night]). Doesn’t do until M> N doesn’t mean that the cycles should continue until the moment when M becomes greater than N, in this case 16? 2. Yes - WebAnalytics

1 answer 1

The cycle is correct, but M substituted in "[Time HH].[Time by Periods].[H].&[M]" .

I would do something like this (there is nothing to check):

 Dim WS_Main As Worksheet Dim WS_Today As Worksheet Dim StartUpdate As Long Dim LastUpdate As Long Dim Params() As String Set WS_Main = ThisWorkbook.Sheets("Stat Today(MainSources)") Set WS_Today = ThisWorkbook.Sheets("Stat Today") StartUpdate = 8 LastUpdate = WS_Main.Cells(2, 10).Value ReDim Params(LastUpdate - StartUpdate) For M = StartUpdate To LastUpdate Params(M - StartUpdate) = "[Time HH].[Time by Periods].[H].&[" & Trim(Str(M)) & "]" Next M WS_Today.PivotTables("СводнаяТаблица13").PivotFields( _ "[Time HH].[Time by Periods].[Period]").VisibleItemsList = Array( _ "[Time HH].[Time by Periods].[Period].&[Night]") WS_Today.PivotTables("СводнаяТаблица13").PivotFields( _ "[Time HH].[Time by Periods].[H]").VisibleItemsList = Params
  • Writes Run-time error '1004' Cannot find item in OLAP club (with added .PivotTables("СводнаяТаблица13") ). I noticed an error in my code before .PivotFields is missing .PivotTables("СводнаяТаблица13") - WebAnalytics
  • And what line at an error highlights? - slippyk
  • WS_Today.PivotFields("[Time HH].[Time by Periods].[H]").VisibleItemsList = Params - WebAnalytics
  • try changing Str(M) to Trim(Str(M)) , the first option inserts an extra space and [Time HH].[Time by Periods].[H].&[ 15] - slippyk