VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep

[willow1985]

I am getting the above runtime error on the line below marked with a ****

If anyone could help me with what I am missing that would be wonderful.

Thank you

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub OTD()

'

' OTD Macro

Dim OTDD As String

Dim OTDN As String

OTDD = Workbooks("Dashboard ").Sheets("List").Range("B46").Value

OTDN = Workbooks("Dashboard ").Sheets("List").Range("B47").Value

Sheets("OTD").Select

Application.EnableEvents = True

For Each lo In ActiveSheet.ListObjects

lo.AutoFilter.ShowAllData

Next lo

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

Cells.EntireColumn.Hidden = False



Workbooks.Open OTDD



Windows(OTDN).Activate

Sheets("1 Data").Range("A1:S1000000").ListObject.QueryTable.Refresh BackgroundQuery:=False '****

Sleep 2

Sheets("2 Data").Range("A1:AB1000000").ListObject.QueryTable.Refresh BackgroundQuery:=False

Sleep 2

Sheets("3 Data").Range("A1:AA1000000").ListObject.QueryTable.Refresh BackgroundQuery:=False

Sleep 2

Sheets("4 Data").Range("A1:AB1000000").ListObject.QueryTable.Refresh BackgroundQuery:=False

Sleep 2

Sheets("Stats").Select

Range("A3:G14").Select

Selection.Copy

Windows("Dashboard.xlsm").Activate

Sheets("OTD").Select

Range("A2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Windows(OTDN).Activate

Sheets("2 Stats").Select

Range("A3:G14").Select

Selection.Copy

Windows("Dashboard.xlsm").Activate

Sheets("OTD").Select

Range("I2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Windows(OTDN).Activate

Sheets("3 Stats").Select

Range("A3:G14").Select

Selection.Copy

Windows("Dashboard.xlsm").Activate

Sheets("OTD").Select

Range("Q2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Windows(OTDN).Activate

Sheets("4 Stats").Select

Range("A3:G14").Select

Selection.Copy

Windows("Dashboard.xlsm").Activate

Sheets("OTD").Select

Range("Y2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Workbooks(OTDN).Close False

Windows("Dashboard xlsm").Activate

Range("A1").Select





End Sub

 

[DanteAmor]

Workbooks.Open OTDD
Windows(OTDN).Activate

Check which workbook you are going to work on, because you are opening the OTDD workbook, but on the next line you activate the OTDN workbook.

Sheets("1 Data").Range("A1:S1000000").ListObject.QueryTable.Refresh BackgroundQuery:=False '****

The error: "VBA Run-time error 91", may be because the sheet "1 Data" does not exist in the workbook "OTDN" or because the table to be updated does not exist in the cell range "A1:S1000000".

Review the workbook you are going to work on, the sheet name, and the cell range.

 

[willow1985]

Check which workbook you are going to work on, because you are opening the OTDD workbook, but on the next line you activate the OTDN workbook.



The error: "VBA Run-time error 91", may be because the sheet "1 Data" does not exist in the workbook "OTDN" or because the table to be updated does not exist in the cell range "A1:S1000000".

Review the workbook you are going to work on, the sheet name, and the cell range.

I changed the code to the one below and it seems to work fine. There was nothing wrong with the names (the names are made up for security reasons for this forum). Still do not know why I was getting the Run-time error 91 but hope the below code will wait long enough for the Connections to update. Will not know for sure until I run it with new data in a few days

Sub OTD()
'
' OTD Macro
Dim OTDD As String
Dim OTDN As String
OTDD = Workbooks("Dashboard ").Sheets("List").Range("B46").Value
OTDN = Workbooks("Dashboard ").Sheets("List").Range("B47").Value
Sheets("OTD").Select
Application.EnableEvents = True
  For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

Workbooks.Open OTDD

Windows(OTDN).Activate
ActiveWorkbook.Worksheets("1 Data").ListObjects("1 Datal") _
    .QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Worksheets("2 Data").ListObjects("2 Data") _
    .QueryTable.Refresh BackgroundQuery:=False
    ActiveWorkbook.Worksheets("3 Data").ListObjects("3 Data") _
    .QueryTable.Refresh BackgroundQuery:=False
        ActiveWorkbook.Worksheets("4 Data").ListObjects("4 Data") _
    .QueryTable.Refresh BackgroundQuery:=False
    
DoEvents

    
Calculate
Sheets("1 Stats").Select
    Range("A3:G14").Select
    Selection.Copy
    Windows("Dashboard .xlsm").Activate
    Sheets("OTD").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
Windows(OTDN).Activate
Sheets("2 Stats").Select
    Range("A3:G14").Select
    Selection.Copy
    Windows("Dashboard .xlsm").Activate
    Sheets("OTD").Select
    Range("I2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
Windows(OTDN).Activate
Sheets("3 Stats").Select
    Range("A3:G14").Select
    Selection.Copy
    Windows("Dashboard .xlsm").Activate
    Sheets("OTD").Select
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
Windows(OTDN).Activate
Sheets("4 Stats").Select
    Range("A3:G14").Select
    Selection.Copy
    Windows("Dashboard .xlsm").Activate
    Sheets("OTD").Select
    Range("Y2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
Workbooks(OTDN).Close False
    Windows("Dashboard .xlsm").Activate
Range("A1").Select


End Sub

 

[DanteAmor]

After this line:

ActiveWorkbook.Worksheets("1 Data").ListObjects("1 Datal") _ .QueryTable.Refresh BackgroundQuery:=False

Add this line for 5 seconds

Application.Wait Now + TimeValue("00:00:05")

If 5 is not enough, you can increase to 10:

Application.Wait Now + TimeValue("00:00:10")

 

[willow1985]

After this line:


Add this line for 5 seconds

Application.Wait Now + TimeValue("00:00:05")

If 5 is not enough, you can increase to 10:

Application.Wait Now + TimeValue("00:00:10")

That works perfect. Thank you!