Refresh Multiple Query Tables

[Mudbutt]

I'm confused on what's happening here. If i step into the code with F8 and go line by line it runs perfectly fine. And if i comment block out the area I've highlighted with ****** for you guys, then it also works. However, if I take away the comment block of the protecting of the sheets, then I get an error message stating I can't manipulate data on a protected sheet, and then I hit "Ok", but the message pops up eight times in a row. So i have to hit "Ok" eight times, which consiquently, is the same about of Query Tables I have in the workbook. The reason I am doing ThisWorkbook.RefreshAll is to refresh the other 7 queries in the workbook.

I know the logic of the code may seem weird, but it's set up fine for my purposes, as far as error handling goes as well as protecting the sheets as best as I can. I have a button set up in one of the worksheets that runs this procedure.

Private Sub UpdateFulfimment_Report()

If Sheets("Summary").ProtectContents = True Then Sheets("Summary").Unprotect "vinny"
Sheets("Summary").Range("G1").Formula = Format(Date - 7, "mm.dd.yy") & " thru " & Format(Date - 1, "mm.dd.yy")
If Sheets("Summary").ProtectContents = False Then Sheets("Summary").Protect "vinny"

If Sheets("Status").ProtectContents = True Then Sheets("Status").Unprotect "vinny"
Sheets("Status").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
If Sheets("Status").ProtectContents = False Then Sheets("Status").Protect "vinny"

If Sheets("Summary").Range("PNA").Value <> 0 Then
    If Sheets("detail w add").ProtectContents = True Then Sheets("detail w add").Unprotect "vinny"
    If Sheets("PNA").ProtectContents = True Then Sheets("PNA").Unprotect "vinny"
    If Sheets("Status").ProtectContents = True Then Sheets("Status").Unprotect "vinny"
    If Sheets("Exclusions").ProtectContents = True Then Sheets("Exclusions").Unprotect "vinny"
    If Sheets("Summary").ProtectContents = True Then Sheets("Summary").Unprotect "vinny"
    ThisWorkbook.RefreshAll
    
    Sheets("PNA").Visible = True
    Sheets("Exclusions").Visible = True
    With Sheets("Summary")
        .Activate
        .Range("Blocked").Formula = "=Table_Database.accdb_qryWFDomesticBlocked12[[#Totals],[Order Qty]]"
        .Range("Disco").Formula = "=Table_Database.accdb_qryWFDomesticDisco11[[#Totals],[Order Qty]]"
        .Range("Allo").Formula = "=Table_Database.accdb_qryWFDomesticAllo10[[#Totals],[Order Qty]]"
        .Range("FourtyEight").Formula = "=Table_Database.accdb_qryWFDomestic48Exclusion13[[#Totals],[Qty]]"
        .Range("NR").Formula = "=Table_Query_from_MS_Access_Database[[#Totals],[Order Qty]]"
        .Range("AvgDays").Formula = "=AVERAGE('detail w add'!V:V)"
        .Range("AvgDaysLate").Formula = "=AVERAGE('detail w add'!Z:Z)"
    End With
*****************************************    
    [COLOR=Red]If Sheets("detail w add").ProtectContents = False Then Sheets("detail w add").Protect "vinny"
    If Sheets("PNA").ProtectContents = False Then Sheets("PNA").Protect "vinny"
    If Sheets("Status").ProtectContents = False Then Sheets("Status").Protect "vinny"
    If Sheets("Exclusions").ProtectContents = False Then Sheets("Exclusions").Protect "vinny"
    If Sheets("Summary").ProtectContents = False Then Sheets("Summary").Protect "vinny"[/COLOR]
******************************************
Else
    Sheets("PNA").Visible = False
    Sheets("Exclusions").Visible = False
    If Sheets("Summary").ProtectContents = True Then Sheets("Summary").Unprotect "vinny"
    Sheets("Summary").Range("Blocked").Formula = 0
    Sheets("Summary").Range("Disco").Formula = 0
    Sheets("Summary").Range("Allo").Formula = 0
    Sheets("Summary").Range("FourtyEight").Formula = 0
    Sheets("Summary").Range("NR").Formula = 0
    Sheets("Summary").Range("AvgDays").Formula = 0
    Sheets("Summary").Range("AvgDaysLate").Formula = 0
    If Sheets("Summary").ProtectContents = False Then Sheets("Summary").Protect "vinny"
End If

End Sub

Any ideas?

 

[p45cal]

It is very likely that the background refresh property of each of the queries is set to True, I think they should all be set to False. I think what is happening is that the code is moving on straight after the .refreshall command, and while the data is being refreshed the sheets are being protected. If you change the Background refresh to False, the code waits for the data to be refreshed completely, before continuing.

 

[Mudbutt]

WOW, you are so right. It worked. Thank you so much!!