run-time error 91 Object Variable or With Block not set. .... EXCEL VBA

This workbook opens up another and updates it - Keep getting the error shown below with  this line highlighted - but not in every sheet it hits - they are random and it will change

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Daily-Trck.png
Here is the code

Sub Auto_Open()
'
' Auto_Open Macro
' Macro updated 2/11/15 by Helen Billingham
'
'Open Daily Tracking.xlsm and refresh all active tabs

    Workbooks.Open ("V:\DPMTS\MATCON\PP&DProjectTeam\TrackingReport\Daily Tracking.xlsm")
    
    Sheets("TwizzlerBonus").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("BonusPckgCandy").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("Halloween").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("CanadianHalloween").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select
    
    Sheets("Holiday").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select
    
    Sheets("CanadianHoliday").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select
    
    Sheets("Valentines").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("CanadianValentines").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("Easter").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    Sheets("CanadianEaster").Select
    Range("J2").Select
    ActiveSheet.Unprotect
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Range("J2").Select

    'Sheets("FallBaking").Select
    'Range("J2").Select
    'ActiveSheet.Unprotect
    'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    'Range("J2").Select
    

'TwizzlerBonus Formatting

    Sheets("TwizzlerBonus").Select
    Range("J2").Select
    Range("A2:AW18").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:G18").Select
    Selection.NumberFormat = "0.00%"
    Range("J2:v18").Select
    Selection.NumberFormat = "#,##0"
    Range("H2:I18").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("w2:w18").Select
    Selection.NumberFormat = "$#,##0"
    Range("x2:Am18").Select
    Selection.NumberFormat = "#,##0"
    Range("An2:An18").Select
    Selection.NumberFormat = "0.00%"
    Range("Ao2:Ar18").Select
    Selection.NumberFormat = "#,##0"
    Range("J2:AS18").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("J2").Select
    
    Columns("AU:AU").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("J2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'BonusPckgCandy Formatting

    Sheets("BonusPckgCandy").Select
    Range("H2").Select
    Range("A2:AP15").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:G15").Select
    Selection.NumberFormat = "0.00%"
    Range("H2:H15").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("I2:T15").Select
    Selection.NumberFormat = "#,##0"
    Range("U2:U15").Select
    Selection.NumberFormat = "$#,##0"
    Range("V2:AK15").Select
    Selection.NumberFormat = "#,##0"
    Range("AL2:AL15").Select
    Selection.NumberFormat = "0.00%"
    Range("AM2:AP15").Select
    Selection.NumberFormat = "#,##0"
    Range("H2:AP15").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("H2").Select

    Range("H2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'Halloween Formatting

    Sheets("Halloween").Select
    Range("K2").Select
    Range("A2:AX405").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H405").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W405").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J405").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X405").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AN405").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP405").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT405").Select
    Selection.NumberFormat = "#,##0"
    Range("AX2:AX405").Select
    Selection.NumberFormat = "$#,##0"
    Range("K2:AT405").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AX").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True
   
'CanadianHalloween Formatting

    Sheets("CanadianHalloween").Select
    Range("G2").Select
    Range("A2:AO41").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S41").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T41").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ41").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK41").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO41").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO41").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'Holiday Formatting

    Sheets("Holiday").Select
    Range("K2").Select
    Range("A2:AW279").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H279").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W279").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J279").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X279").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AN279").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP79").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT279").Select
    Selection.NumberFormat = "#,##0"
    Range("K2:AT279").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AW").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'CanadianHoliday Formatting

    Sheets("CanadianHoliday").Select
    Range("G2").Select
    Range("A2:AO55").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S55").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T55").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ55").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK55").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO55").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO55").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True


'Valentines Formatting

    Sheets("Valentines").Select
    Range("K2").Select
    Range("A2:AW215").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H215").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W215").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J215").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X215").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AM215").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP215").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT215").Select
    Selection.NumberFormat = "#,##0"
    Range("K2:AT215").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AW").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'CanadianValentines Formatting

    Sheets("CanadianValentines").Select
    Range("G2").Select
    Range("A2:AO29").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S29").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T29").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ29").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK29").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO29").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO29").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True


'Easter Formatting

    Sheets("Easter").Select
    Range("K2").Select
    Range("A2:AX355").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("H2:H355").Select
    Selection.NumberFormat = "0.00%"
    Range("K2:W355").Select
    Selection.NumberFormat = "#,##0"
    Range("I2:J355").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("X2:X355").Select
    Selection.NumberFormat = "$#,##0"
    Range("Y2:AN355").Select
    Selection.NumberFormat = "#,##0"
    Range("AO2:AP355").Select
    Selection.NumberFormat = "0.00%"
    Range("AQ2:AT355").Select
    Selection.NumberFormat = "#,##0"
    Range("AX2:AX355").Select
    Selection.NumberFormat = "$#,##0"
    Range("K2:AT355").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("K2").Select
    
    Columns("AV:AX").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True

'CanadianEaster Formatting

    Sheets("CanadianEaster").Select
    Range("G2").Select
    Range("A2:AO48").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = True
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("G2:S48").Select
    Selection.NumberFormat = "#,##0"
    Range("T2:T48").Select
    Selection.NumberFormat = "$#,##0"
    Range("U2:AJ48").Select
    Selection.NumberFormat = "#,##0"
    Range("AK2:AK48").Select
    Selection.NumberFormat = "0.00%"
    Range("AL2:AO48").Select
    Selection.NumberFormat = "#,##0"
    Range("G2:AO48").Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("G2").Select

    Range("G2").Select

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
        :=True


'FallBaking Formatting

    'Sheets("FallBaking").Select
    'Range("K2").Select
    'Range("A2:AX43").Select
    'With Selection
    '    .VerticalAlignment = xlBottom
    '    .WrapText = False
    '   .Orientation = 0
    '    .ShrinkToFit = True
    '    .MergeCells = False
   'End With
    'With Selection.Font
     '   .Name = "Arial"
     '   .FontStyle = "Regular"
     '   .Size = 8
     '   .Strikethrough = False
     '   .Superscript = False
     '   .Subscript = False
     '   .OutlineFont = False
     '   .Shadow = False
     '   .Underline = xlUnderlineStyleNone
     '   .ColorIndex = 1
    'End With
    'Range("H2:H43").Select
    'Selection.NumberFormat = "0.00%"
    'Range("K2:W43").Select
    'Selection.NumberFormat = "#,##0"
    'Range("I2:J43").Select
    'Selection.NumberFormat = "m/d/yyyy"
    'Range("X2:X43").Select
    'Selection.NumberFormat = "$#,##0"
    'Range("Y2:AN43").Select
    'Selection.NumberFormat = "#,##0"
    'Range("AO2:AP43").Select
    'Selection.NumberFormat = "0.00%"
    'Range("AQ2:AT43").Select
    'Selection.NumberFormat = "#,##0"
    'Range("AX2:AX43").Select
    'Selection.NumberFormat = "$#,##0"
    'Range("K2:AT43").Select
    'Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    '    SearchOrder:=xlByRows, MatchCase:=False
    'Range("K2").Select
    
    'Columns("AV:AX").Select
    'Selection.Locked = False
    'Selection.FormulaHidden = False
    'Range("K2").Select
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    '   , AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowFiltering _
    '    :=True
 

       
'Return to first worksheet

    Sheets("TwizzlerBonus").Select
    Range("J2").Select
         
    ActiveWorkbook.Save
            
            ChDir "V:\DPMTS\MATCON\PP&DProjectTeam\TrackingReport"
            ActiveWorkbook.ProtectSharing Filename:= _
            "V:\dpmts\Matcon\PP&DProjectTeam\TrackingReport\DailyTracking" & Format(Date, "M-D-YYYY")
   
            
    Application.Quit
     
End Sub

Open in new window

.
Any ideas - it must be an easy fix...... No?
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Is there any code in the Worksheet_SelectionChange events in the other workbook?
dawber39Database Analyst / Application DeveloperAuthor Commented:
There is not - in any of the sheets "Holiday, Halloween, Easter,.. etc"

Just a macro in a module - which I don't think is even called to - this is it:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/7/2008 by DS

'
    Sheets(Array("Easter", "CanadianEaster", "TwizzlerBonus", "CanadianSummer", _
        "BonusPkgCandy")).Select
    Sheets("Easter").Activate
End Sub

Open in new window

Martin LissOlder than dirtCommented:
The reason I asked is that while I may be missing something, the code you posted looks OK, but if there's an un-handled error in the other workbook, that error may show up in the main workbook. Would it be possible for you to post the two workbooks?

Also, did this problem start after the 2/11/15 changes?
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

dawber39Database Analyst / Application DeveloperAuthor Commented:
I am not sure when it started - I do know it was handed to me this morning by a woman that is currently converting Access, and Excel Applications to 2013 from 2003

How this works - open up the macro enabled copy with my name Richard on it - and it should open Daily Tracking. I know there is an external database involved with some local tables - and some linked Oracle tables - I can send that as well if need be
Macro-Workbook---CopyRichard.xlsm
Daily-Tracking.xlsm
Martin LissOlder than dirtCommented:
I'm not familiar with  querytables tables, but from what I've just read, querytables are "built from data returned from an external data source". When I run your code I get the error on the very first sheet and if I do Msgbox activesheet.querytables.count, i not surprisingly get 0. So perhaps the problem is that you are net getting external data for all the sheets all of the time. If so then something like this (where I added lines 2 and 7) for every sheet will fix the problem.

    Sheets("TwizzlerBonus").Select
    If ActiveSheet.QueryTables.Count > 0 Then
        Range("J2").Select
        ActiveSheet.Unprotect
        Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
        Range("J2").Select
    End If

Open in new window

dawber39Database Analyst / Application DeveloperAuthor Commented:
Now I get error 'Object Doesn't Support This Property or Method - with this Highlighted (If ActiveSheet.QueryTable.Count > 0 Then) - if I step through it - I get the original error also
Martin LissOlder than dirtCommented:
If that's your actual code then QueryTable should be QueryTables.
dawber39Database Analyst / Application DeveloperAuthor Commented:
okay - I will try that
dawber39Database Analyst / Application DeveloperAuthor Commented:
Same thing - even after closed and re-open
Martin LissOlder than dirtCommented:
Which version of Excel are you using?
dawber39Database Analyst / Application DeveloperAuthor Commented:
2013
Martin LissOlder than dirtCommented:
Okay that may explain it because the code works in Excel 2010 and I don't have 2013 so unfortunately I can't help any further. So while someone else may chime in here, it's unlikely so you should probably start a new question and mention that it's 2013 and what I suggested as the solution.
Ejgil HedegaardCommented:
Probably some of the tables are created in Excel 2003 and are not Listobjects.
It looks as if that is the case for the sheets Halloween, CanadianHalloween, CanadianHoliday and CanadianEaster.

Try change
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
to
ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
for those sheets
dawber39Database Analyst / Application DeveloperAuthor Commented:
That does sound relevant - but

Funny thing is - those sheets you mentioned are the ones the code runs fine in stepping through it with break points, and it is the others TwizzlerBonus, BonusPckgCandy, Holiday, Valentines, and Easter - that fail.... any ideas?
Martin LissOlder than dirtCommented:
Do those sheets have QueryTables when they fail?
dawber39Database Analyst / Application DeveloperAuthor Commented:
Yes
dawber39Database Analyst / Application DeveloperAuthor Commented:
Tried it with Table as it was - and Tables as it should be - with same results
Ejgil HedegaardCommented:
When I open Daily-Tracking I get an error message about unreadable content.
In the repaired workbook the sheets I mention + the hidden sheets FallBaking2 and Combined don't have a Listobject.
But there are no QueryTable either on the other sheets.
Guessed that was due to the error.
Only the hidden sheet FallBaking2 has a querytable, and the table looks like the data on the other sheets.

To update QueryTables I use the syntax
Worksheets("Sheetname").QueryTables(1).Refresh BackgroundQuery:=False
or sometimes with the 1 replaced by the table name, but typically there are only one on the sheet.

And for update of Listobjects querytables I use the syntax
Worksheets("Sheetname").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

It is not needed to select the sheet, or anything on the sheet to update the table.

Make a new sheet named Sheet1 in Daily-Tracking, and insert this macro in a module, and run to see if the sheet have ListObjects or QueryTables.
Sub ListTables()
    Dim rw As Integer, ws As Worksheet, wsTableList As Worksheet
    Set wsTableList = Worksheets("Sheet1")
    wsTableList.Cells.ClearContents
    wsTableList.Range("A1:C1") = Array("Sheet name", "ListObjects", "QueryTables")
    rw = 1
    For Each ws In Worksheets
        If ws.Name <> wsTableList.Name Then
            rw = rw + 1
            wsTableList.Cells(rw, 1) = ws.Name
            wsTableList.Cells(rw, 2) = ws.ListObjects.Count
            wsTableList.Cells(rw, 3) = ws.QueryTables.Count
        End If
    Next ws
End Sub

Open in new window


To unprotect all worksheets and update the tables, instead of doing it one at a time, you could use
Sub UnprotectAndUpdateTables()
Dim ws As Worksheet, iSheets As Integer, iTables As Integer
    For Each ws In ActiveWorkbook.Worksheets
        iSheets = iSheets + 1
        ws.Unprotect
        If ws.ListObjects.Count > 0 Then
            ws.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            iTables = iTables + 1
        End If
        If ws.QueryTables.Count > 0 Then
            ws.QueryTables(1).Refresh BackgroundQuery:=False
            iTables = iTables + 1
        End If
        If iSheets <> iTables Then
            MsgBox "Sheet " & ws.Name & "has no table"
        End If
    Next ws
End Sub

Open in new window

But you need to get it working first.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawber39Database Analyst / Application DeveloperAuthor Commented:
I will give this a shot later on today - I appreciate your help - this has been one pain after another with legacy  apps and what not being converted to 2013. I am almost positive the person is not doing it correctly either - but I get to fix them. If I run into more issues trying it this way - I will post another question. Thanks again
dawber39Database Analyst / Application DeveloperAuthor Commented:
These people are great up here - the membership is well worth it - Thinking its getting close to upgrade time
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.