VBA to change source of multiple power query connections

[kcmuppet]

Hello I've a got sheet with 10 queries that load to Tables

I want to make a copy of the sheet with tables populated using the same query but with a different data source, but preserving the table formats, etc.

So, I copy the sheet, which automatically creates new queries, like:

Query 1 Name Group 1 (2)
Query 2 Name Group 1 (2)
Query 3 Name Group 1 (2)
...
Query 10 Name Group 1 (2)

Using a manual process, I then open the the query editor, click the source step at the top and change the source line for each query from something like

= #"Data source for Group 1"

to

= #"Another data from another connection"

Then I close the editor and click "Keep", and then once the refreshes have completed copied table on the new sheet now has the correct data in it. It all works, but to do the whole sheet takes more than 40 minutes, by the time the queries have loaded into the editor and, saved and then refreshed and the data loaded to the tables.

It is possible to use VBA instead to change the source for a list of specific connections?

If so, it would be handy also to:
- first generate a list of connection names, so that these can be used to specify the correct connections to update - OR if it's easier just constrain the source changes to connections on the active sheet?
- rename the updated copied queries/connections,

e.g.
Query 1 Name Group 2
Query 2 Name Group 2
Query 3 Name Group 2
etc.

instead of the automatically created
Query 1 Name Group 1 (2)
Query 2 Name Group 1 (2)
Query 3 Name Group 1 (2)
etc.