correction in vb code required pivot refresh lock sheet

[ateebali]

Dear Sir,
I am using following code to refresh my pivot table which is on same sheet as of data
The sheet is protected as well since I dont want user to change the format but it is not working on lock sheet, in unlock sheet, its working fine.

Sub Pivot()
'
' Pivot Macro
'


'
ActiveWindow.SmallScroll Down:=24
Range("A37").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

 

[ateebali]

Requesting for earliest resolution for this.

 

[Kamolga]

Unprotect your sheet manually and protect it for user but not macro using userinterfaceonly. Change blue to your parameters

[LEFT][COLOR=#666666][FONT=Lora]Sheets(“[/FONT][/COLOR][COLOR=#0000ff]sheetname[/COLOR][COLOR=#666666][FONT=Lora]”).Protect Password:="[/FONT][/COLOR][COLOR=#0000ff]Secret[/COLOR][COLOR=#666666][FONT=Lora]", UserInterFaceOnly:=True[/FONT][/COLOR][/LEFT]

else (but more complicated)

[FONT=Lora][COLOR=#666666]Sheets(“[/COLOR][/FONT][COLOR=#0000ff][COLOR=#0000ff][FONT=Verdana]sheetname[/FONT][/COLOR][/COLOR][COLOR=#666666][COLOR=#666666][FONT=Verdana][FONT=Lora]”).unprotect[/FONT][/FONT][/COLOR][/COLOR]

[COLOR=#008000]'Your code[/COLOR]

[LEFT][COLOR=#222222][FONT=Lora][COLOR=#666666]Sheets(“[/COLOR][/FONT][/COLOR][COLOR=#0000ff][COLOR=#0000ff][FONT=Verdana]sheetname[/FONT][/COLOR][/COLOR][COLOR=#666666][COLOR=#666666][FONT=Verdana][FONT=Lora]”).Protect[/FONT][/FONT][/COLOR][/COLOR]
[/LEFT]

 

[ateebali]

Sir not working, can you please send complete coding so I just need to paste them, currently I do like this which is not workable;



Sub Pivot()


Sheets(“Software”).Unprotect
'0


' Pivot Macro
'


'
ActiveWindow.SmallScroll Down:=30
Range("A37").Select
ActiveSheet.PivotTables("Summary").PivotCache.Refresh





Sheets(“Software”).Protect
'


End Sub

 

[ateebali]

Or if there is a way that pivot automatically refreshed
or if we have a formula to consolidate the data like pivot do?

 

[Kamolga]

Or if there is a way that pivot automatically refreshed

Unprotect your sheet, then right click the sheet name and click view code. Now paste

Private Sub Worksheet_Activate()
ActiveSheet.Protect Password:="[COLOR=#0000ff]Secret[/COLOR]", UserInterFaceOnly:=True
ActiveWorkbook.RefreshAll
End Sub

Everytime you want to refresh the pivot, leave the sheet and come back (it will also protect the sheet).