Help with Error 91

[dbmagnus]

This is probably an easy question, but my novice experience and lack of applicable search results have left me in a bind. I'm trying to write a simple macro that when run will refresh X number of data blocks (queries) in the file.

First the error:
Run time error 91: Object variable or With block variable not set.

Now the code:
[vba]Public Sub refresh_report()
Dim ct As Integer
Dim i As Integer
Dim qt As QueryTable
Dim shtData As Worksheet
Dim shtReport As Worksheet

ct = shtData.QueryTables.Count

i = 0

For Each qt In shtData.QueryTables

i = i + 1
shtReport.Range("B21").Value = "Refreshing data block " & i & " of " & ct & "..."

qt.BackgroundQuery = False
qt.Refresh

Next qt
shtReport.Range("B21").ClearContents
End Sub[/vba]

It is erroring here:
ct = shtData.QueryTables.Count

I would think there's any easy solution but I haven't found it yet. Thanks for any help you could give this novice.

-Dan

[Bob Phillips]

Just use

[vba]

ActiveWorkbook.RefreshAll
[/vba]

[dbmagnus]

That works. Of course it doesn't do the fancy display of which query of how many are refreshing, though in reality I don't think that is even necessary.

Thanks for the reply.

[Blade Hunter]

Is it because you have not assigned anything to your variable: shtData?

maybe use: shtData = activesheet (If this is the case it negates the need for shtData altogether anyway)

[dbmagnus]

The response by xld was still giving me some problems, so I played around with it some more and I think I found the code the for now seems to be working the best:

[vba]Public Sub refreshtest2()
Dim qt As QueryTable
For Each qt In Worksheets("Data").QueryTables
qt.refresh
Next

End Sub[/vba]

Now if someone could just tell me how to clear the contents of a cell once a new date is selected in one of the Calendar Controls that would be helpful (though that may require a separate post).