this is my first post here 🙂
Maybe some details at the beginning.
I have three pivot charts based on pivot tables. I have some slicers to these charts as well.
I can choose no. of week at slicer so I can see data exactly for that week on chart.
For example, today is 7th week of 2021. Filter is built like 2020_54, 2021_01, 2021_02…
And my goal is to find a way to automatic refresh of slicer so when the user open the file he will see data on charts just for specific date range. Without clicking anything on slicers manually. “Date from” should be always 2021_01 (from first week of the year). “Date to” should be actual number of week.
And I wrote today some vba code. I don’t have much experience in that stuff. Let’s take a look.
Sub subrefreshpivots()
Dim endDate as date
Dim nrtyg as string
‘refresh “date to” (there is =TODAY() function in cell named “endDate” in “shtDane” sheet)
With shtDane
Range(“endDate”).Select
Calculate
End With
‘ value assignment from “endDate” cell to variable called endDate
shtDane.Range(“endDate”).value = endDate
‘which no. of week we have right now
nrtyg = Application.WorksheetFunction.WeekNum(endDate) – 1
With shtDane
With Activesheet.PivotTables(“mypivottable”).PivotFields(“planned week”)
if nrtyg = 1 then .PivotItems(“1”).Visible = True
if nrtyg = 2 then .PivotItems(“1”).Visible = True and .PivotItems(“2”).Visible = True
…
…
end with
end with
end sub
My question is how to write some sort of loop just to not repeat this if – then statement and that the code will be clean. I cannot use macro recorder to record a loop.
And my second request. I would like to end macro as soon as possible when the condition is TRUE so the macro doesn’t need to check other if – then statements after TRUE condition.
Thanks for your help.