Confused with VBA - Macro works fine in one workbook, but when I copy same code into another workbook, it doesn't work

[Jmorrison67]

Good morning All,

I'm looking for some help with what is probably a simple bit of VBA coding. I am not familiar with VBA and got the code from online biccountant i think it was.

What the code is intended to do is:
Unprotect a worksheet named "Consol" >>> Consol is the output of a power query
Refresh the query
Protect the worksheet again

It works fine one the 1st workbook i tried it in but when i use the name code, but changing the query name, it doesnt work....tried googling for 3+ hours to find a solution but REALLY struggling.

Code that works in workbook 1:

Sub Refresh_Report()

Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolPSC").Refresh

Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub


Code that doesnt work in workbook 2:

Sub Refresh_Report()

Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub



Only thing i changed was the query name from ConsolPSC to ConsolDist as the ConsolPSC is the name of the query in workbook 1 and Consol Dist the name in workbook 2. My thinking was that i would need to define the 'Activeworkbook', maybe rename it to whatever the workbook is called - i'm sure it is something really simple.

The error i get in the 2nd workbook is:
which is ofcourse the standard message you get when you try to enter into a protected worksheet.

Any help would be appreciated

Kind regards
Jmorrison67

 

[sykes]

You're most welcome, and thanks for the feedback - sorry it took a bit of time to get there! The first WB's query must have had the Background refresh off, but when you did the second, it must have defaulted to on.
As you said - good note to end the week on.

Could you just mark the post #8 as the "Solution" - I think only you as the OP, can do this - it should then show up in green, which will save other Folk who research the same issue, from having to trawl down through the whole thread, to find the solution that worked for you.

Thanks.

All the best.
Sykes.