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

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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.
1629452832955.png



Any help would be appreciated

Kind regards
Jmorrison67
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,223
Messages
5,768,902
Members
425,501
Latest member
sunderlalrwr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top