VBA code required to refresh multiple queries on different tabs, unprotecting and then reapplying the password

[Jmorrison67]

Good evening MrExcel Community,

I'm looking for a bit of help with VBA (again)
I thought it would be quite straight forward what i'm trying to do having managed alrightish with my 1st VBA project the other week....so here goes:

Original VBA for refresh based on one tab, with output in the tab a power query
(tab names and passwords changed)

Sub Refresh_Report()
Sheets("Peter").Unprotect Password:="PETER"
ActiveWorkbook.Connections("Query - PeterDist").Refresh
Sheets("Peter").Protect Password:="PETER"
MsgBox "All data has now refreshed"
End Sub

(Above works fine)

I have 3 more tabs which have their own queries. On each of the tabs I have a ‘refresh’ button which has the above code – switching out the tab and query names. Everything runs fine.

What I have now been asked to do is create a macro which allows the user just to hit ‘refresh’ button once, not 4 times (once per tab, which has a different query on it), so I thought it would just be a case of stacking the code up as follows:

Sub Refresh_ALL()

Sheets("Peter").Unprotect Password:="PETER"

Sheets("James").Unprotect Password:="JAMES"

Sheets("Toby").Unprotect Password:="TOBY"

Sheets("Robert").Unprotect Password:="ROBERT"


ActiveWorkbook.Connection("Query - PeterDist").Refresh

ActiveWorkbook.Connection("Query - JamesDist").Refresh

ActiveWorkbook.Connection("Query - TobyDist").Refresh

ActiveWorkbook.Connection("Query - RobertDist").Refresh


Sheets("Peter").Protect Password:="PETER"

Sheets("James").Protect Password:="JAMES"

Sheets("Toby").Protect Password:="TOBY"

Sheets("Robert").Protect Password:="ROBERT"


MsgBox "All data has now refreshed"


End Sub



When I run this and assign the code to a refresh button 1. The ‘sub bit at the start turns a maroon colour with a dot in the left margin – I have no idea what that mean??

If I try and run the macro in excel the sub turns yellow and nothing happens:

Can someone advise if the code is completely wrong?

Ultimate goal is to allow the user to simply click a refresh button which gives them the most up to view / position. All the tabs Peter, James, Toby, Robert are protected with a password and the code should be unlocking each of the tabs, refreshing the data and then reapplying the passwords. I’m sure there is probably an easier way of doing it.

Any help would be much appreciated
KR Jmorrison67

 

[Michael M]

OK, this way then.....

Sub MM1()
Dim MyArr As Variant, ws As Worksheet
Application.ScreenUpdating = False
 MyArr = Sheets(Array("Consol", "Full List of Projects", "Projects with Benefits built in", "Efficiency No Paybacks"))
For Each ws In MyArr
        ws.activate
        ws.Unprotect Password:="PETER"
        If ws.Name = "Consol" Then ActiveWorkbook.Connection("Query - ConsolDist").Refresh
        If ws.Name = "Full List of Projects" Then ActiveWorkbook.Connection("Query - FullListDist").Refresh
        If ws.Name = "Projects with Benefits built in" Then ActiveWorkbook.Connection("Query - BenefitsDist").Refresh
        If ws.Name = "Efficiency No Paybacks" Then ActiveWorkbook.Connection("Query - EfficiencyDist").Refresh
        ws.Protect Password:="PETER"
Next ws
Application.ScreenUpdating = True
End Sub

 

[Michael M]