Forum Discussion
Need some help with VBA
Did you use the method described at https://www.excelcampus.com/vba/refresh-pivot-tables-automatically/?
I was able to accomplish as:
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").Unprotect
ThisWorkbook.RefreshAll
Worksheets("Pivot").Protect
End Sub
Workheets("Pivot") is Protected and cells cannot be selected.
ChrisMendoza Yeah i tried it before. first problem was that code is running in loop so my excel file crash. the second problem is that for the pivot to update the sheet has to open and then close. If i have 30-50 sheets to work on then that would be too tiring to type in the same password always. If in the meeting the file need a update i have to type in for all those sheets.
What i want is a code which will run in the background and just update the pivot tables i have in this 30-50 sheets without typing the password and not the whole file which crash.I need this code because i have formulas written on every sheet. when i copy paste it or want to share the file. other people can see the formulas. Thats why the file is closed and i want to update the pivot table in closed/protected sheets.
Thanks
Why would you have to type the password each time on multiple sheets? You input the Password as an argument like:
Worksheets(currentSheetName).Unprotect Password:="SuperSecretPassword" . . . Worksheets(currentSheetName).Protect Password:="SuperSecretPassword", userinterfaceonly:=TrueYou should also probably be using as a wrapper:
Application.ScreenUpdating = False . . . Application.ScreenUpdating = TrueI would also recommend that rather than Worksheet_Change using something like Workbook_BeforeClose or Workbook_BeforeSave. In these ways, you're not always running the procedure.
ChrisMendoza I new to VBA coding so what i understood and wrote the code based on your comment. if you could check It looks like this:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Worksheets(Sheet1).Unprotect Password:="password"Me.PivotTable(1).RefreshTable
Application.ScreenUpdating = False
Application.ScreenUpdating = True
Worksheets(Sheet1).Protect Password:="password", userinterfaceonly:=True
End Subnow the next thing i didnt understand is when i wrote this code nothing happened. in protected or unprotcted sheet this code didnt run. what am i doing wrong here?
And do i have to always open the VBA application for the code to work?
When i wrote the code for update only it was always updating the pivot when i input a new data without opening VBA. but now i want to protect the sheet and run the code in background without opening VBA after writing the new code.
Thanks
