Hi all,

I am new to VBA and I have been trying to write (I puzzled some codes from the web) the below code.

The purpose of the code is:

- unprotect the all Workbook
- Refresh the power query connections & data model
- protect the workbook again but allow filtering of table, the outlining and the slicers of the Power pivot .

I am not sure if the why I have written/ puzzled the code is the optimal.

Btw I run the code through a button in the worksheet (when clicking on the button the code runs)

Everything works except for the Power pivot slicer, and for the fact that I get a message at the end that the Worksheet is protected (I would like to not get this pop up message).

Thanks in advance for any help.

KR

------------------------------------------------------------------
------------------------------------------------------------------
Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)


For Each rs In Worksheets
rs.Unprotect Password:="123"
Next rs
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ActiveWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn


For Each ra In Worksheets
With ra
.Protect Password:="123", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
.AllowUsingPivotTables = True
End With
Next


Worksheets("INPUT Table").Activate


End Sub

------------------------------------------------------------------
------------------------------------------------------------------