I have a sheet with source data and a pivot table in the same sheet. I have protected the sheet so users are not able to select certain cells or change them. By protecting the sheet, the refresh option on the pivot table has been disabled. I am working on a macro that will auto refresh the table. Here is some code that I have tried but it does not work. Please advise on how to get this to working. Thank you.

Private Sub worksheet_change(ByVal target As Range)


With ActiveSheet
'Application.EnableEvents = False
.Unprotect Password:=" "
Sheet1.PivotTables("PivotTable1").PivotCache.refresh
.Protect Password:=" ", AllowUsingPivotTables:=True
'Application.EnableEvents = True

End With


End Sub