Combining Macro for Refreshing Queries across multiple protected sheets

[Matt_C_Paradox]

Hi Im new to VBA so really have very little to go on. I found two scripts, one for refreshing queries in protected sheets and another which runs the former across multiple sheets at once i tried to put the code together as below but was met with a error regarding a "rogue end sub" 

Private Sub workbook_open()
Sub Dosomething()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call RunCode
Next
Application.ScreenUpdating = True
End Sub
Sub RunCode()
'your code here
Sub DataRefresh()
'Update by Extendoffice 5/28/2019
ActiveSheet.Unprotect "123"
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:00:01"), "DataRefresh2"
End Sub
Sub DataRefresh2()
If Application.CommandBars.GetEnabledMso("RefreshStatus") Then
Application.OnTime Now + TimeValue("00:00:01"), " DataRefresh2"
Else
ActiveSheet.Protect "123"
End If
End Sub
End Sub

I really have no idea what's gone wrong

[#2 · Accepted solution]

@Matt_C_Paradox If I understood well, this is what needs to be done.

This bit goes in the ThisWorkbook module:

Private Sub workbook_open()
    UnprotectAndRefresh
End Sub

And this part goes in a normal module (Insert, Module):

Sub UnprotectAndRefresh()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Unprotect "123"
    Next
    
End Sub

Sub DataRefresh()
'Update by Extendoffice 5/28/2019
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:01"), "IsRefreshDone"
End Sub

Sub IsRefreshDone()
    Dim xSh As Worksheet
    If Application.CommandBars.GetEnabledMso("RefreshStatus") Then
        'Refresh still running, schedule another check in 1 second
        Application.OnTime Now + TimeValue("00:00:01"), " IsRefreshDone"
    Else
        'Refreshing is done, reprotect sheets
        For Each xSh In Worksheets
            xSh.Protect "123"
        Next
    End If
End Sub

[#3]

Awesome! it works like a dream... I've credited you in a comment at the end

Am I right in thinking the major change you made was defining the module first then introducing what it does?

[#4]

That, and making sure each routine is on its own, starting with "Sub RoutineName" and ending with "End Sub", your example code had multiple routines "nested" in each other, that is invalid syntax.