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

 

[Jmorrison67]

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

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

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

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

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

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

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

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


Apologies - the password is the same for all sheets - not different for each one - that might help

 

[Michael M]

Firstly the Maroon dot is a Bookmark....click on the little round Maroon dot again to remove it and the code will run.
Second I don't have PowerQuesry, but you could have simply written the code x number of times and then simply called each macro

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

Then used

Sub RefreshQuery()
Call Refresh_Peter
Call Refresh_Robert
end sub

 

[Jmorrison67]

Firstly the Maroon dot is a Bookmark....click on the little round Maroon dot again to remove it and the code will run.
Second I don't have PowerQuesry, but you could have simply written the code x number of times and then simply called each macro

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

Then used

Sub RefreshQuery()
Call Refresh_Peter
Call Refresh_Robert
end sub

Good morning Michael,

Thanks for your reply.

This is my 2nd ever VBA project so still coming to terms with navigating around.

Doing this part, does the job - but wonder if there is any improvements I/we can make to it?

Sub RefreshQuery()
Call Refresh_Peter
Call Refresh_Robert
end sub

Just one follow up - I have the refresh button on the 'Contents' page - when I run this macro, I watch it going through its 'refresh' process by flicking through the tabs Peter, Robert, James, Toby and then it finishes on the tab which I have 'call' last in the list. Is there a way to run the macro and staying on the contents page?

Also is there a way I can change the code in the 4 seperate macro's Refresh_Peter, Refresh_Robert, Refresh_James, Refresh_Toby so that when the 'Call' macro runs the user doesnt have to click the 'All data has now refreshed' 4 times, just once at the end? One final thing, my manager is quite reluctant to use power query's and macro's as prefers basic excel and is concerned with the time running this which is c30 seconds (i'm pro this route and what's a 30 sec delay , just wondering if there is anything I can do to speed up the macro?

Your insight is most valuable

KR Jmorrison67

 

[Michael M]

Is it ALL sheets except for the "Contents" Sheet ??

 

[Jmorrison67]

Is it ALL sheets except for the "Contents" Sheet ??

Good morning Michael,

My workbook has 19 tabs.

Contents is on tab 1
'Peter' is on tab 19
'Robert' is on tab 5
'Toby' is on tab 6
'James' is on tab 7

So the call job finishes on tab 7, but need the job either not to move tabs as its running or return to contents page when its done. Hope that makes sense

Appreciate your help

KR Jmorrison67

 

[Michael M]

So there are other worksheets that don't need to be "Refreshed" ??
AND
It's only 4 sheets to update ??

 

[Michael M]

I don't have Excel ATM, but I believe you will need something like this (UNTESTED)

Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "James" Or ws.Name = "Robert" Or ws.Name = "Toby" Or ws.Name = "Peter" Then
        With ws
                .Unprotect Password:=UCase(ws.Name)
                ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh
                .Protect Password:=UCase(ws.Name)
        End With
    End If
    Next ws
MsgBox "All data has now refreshed"
End Sub

Hopefully someone else can provide further information if required

 

[Jmorrison67]

So there are other worksheets that don't need to be "Refreshed" ??
AND
It's only 4 sheets to update ??

Morning Michael,

Yes just 4 Power Query outputs on just 4 tabs. The other tabs are user input info, with the main Power Query 'Peter' consolidating all the info into one sheet. Peter is the most important of all the outputs.

Regards
Jmorrison67

 

[Jmorrison67]

I don't have Excel ATM, but I believe you will need something like this (UNTESTED)

Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "James" Or ws.Name = "Robert" Or ws.Name = "Toby" Or ws.Name = "Peter" Then
        With ws
                .Unprotect Password:=UCase(ws.Name)
                ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh
                .Protect Password:=UCase(ws.Name)
        End With
    End If
    Next ws
MsgBox "All data has now refreshed"
End Sub

Hopefully someone else can provide further information if required

Good morning Michael,

Thanks - i'll give it a crack this morning. Just to clarify, do I need to write this part 4 times or is there an AND or comma function I can use or is it simply just as the code reads? and where you have (ws.Name) do I enter what the worksheet is called?

With ws
.Unprotect Password:=UCase(ws.Name)
ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh
.Protect Password:=UCase(ws.Name)

Example:
With ws
.Unprotect Password:= PETER(ws.Peter)
ActiveWorkbook.Connection("Query - " & ws.Peter & "Dist").refresh
.Protect Password:= PETER(ws.Peter)

Thanks again

KR Jmorrison67