pivot table automatically refresh

In the work sheet module for the tab you are opening, put the following code and change the sheet name and pivot table name as indicated.

Private Sub Worksheet_Activate()
Sheets("SheetNameWithPivot").PivotTables("PivotTableName").PivotCache.Refresh
End Sub

Add this to the code

Sheets("SheetName").Protect userinterfaceonly:=True

Put the statements in the other order

Private Sub Worksheet_Activate()
Sheets("Zoekfuncties").Protect userinterfaceonly:=True
Sheets("Zoekfuncties").PivotTables("Draaitabel1").PivotCache.Refresh
End Sub

I should have caught that you were doing this in a worksheet module. You could use ActiveWorksheet instead of naming the sheet. However, explicitly naming the sheet is never wrong and a good habit to get into.

I had to learn this one myself since I've never done it on a password protected sheet.

Private Sub Worksheet_Activate()
Sheets("Zoekfuncties").Protect userinterfaceonly:=True, Password:="Your Password"
Sheets("Zoekfuncties").PivotTables("Draaitabel1").PivotCache.Refresh
End Sub

I duplicated your exact scenario, according to the articles on line the code is supposed to work.

Here is an inefficient, but works version of the code

Private Sub Worksheet_Activate()
MsgBox "Hello, you have selected the sheet"
ActiveSheet.Unprotect Password:="Password"
ActiveSheet.PivotTables(1).PivotCache.Refresh
ActiveSheet.Protect Password:="Password"
End Sub

I went out to this board with the question and they came up with

Private Sub Worksheet_Activate()
ActiveSheet.Protect Password:="koss", userinterfaceonly:=True
Me.PivotTables("Draaitabel1").RefreshTable
End Sub

I learned something too!