Type Error in For Each Worksheet Loop

[acuityman99-ng]

Here's the problem I'm having with the code below.

If the worksheet contains no query-table the codes exits the inner loop
(For Each qt) and moves to the next worksheet with no error.

However, when the worksheet does contains a query-table the code exits
the inner loop but fails when it hits the 'Next ws'.
At that point it returns a "'Run-time error '13': type mismatch". If I
choose debug and then resume the codes continues until it encounters
the next sheet with a query-table.

Any advice will be appreciated
Buck
=====================================================

Dim ws As Worksheet
Dim wsName As String
Dim pt As PivotTable
Dim ptname As String
Dim qt As QueryTable
Dim qtname As String

'Loop through all sheets and updates all QueryTables
Set wb = ActiveWorkbook
For Each ws In wb.Sheets
wsName = ws.Name
For Each qt In ws.QueryTables
qtname = qt.Name
Worksheets(wsName).Activate
Range("A1").Activate
Worksheets(wsName).QueryTables(qtname).Refresh
BackgroundQuery:=False
Next qt
Next ws

 

[Mike Fogleman]

Your code worked perfect for me after one additional line:

Dim wb As Workbook

Mike F

 

[Gary Keramidas]

as mike mentioned, works fine with the added line. add option explicit as
the first line, then run your code. you should get a variable undefined
error.
then add the line mike mentions, your code should run.

if you always use option explicit, it will help you debug these kinds of
errors

 

[Buck]

Thanks for the response but I was actually hoping you guys would find a
problem with the code.

The code works fine for me as long as it is in a workbook I create. If
I send the workbook I created to a coworker, it works fine.

However, if a coworker pastes the code into a workbook they create,
they get the run-time error 'Type mismatch'. If they send me the
workbook they created, I get the same error they do.

We are all using the same version of Excel and I've verified that we
all have the same references. I'm mystified.

 

[Dave Peterson]

For Each ws In wb.Sheets
says to look through all sheets. If you have chart sheets, you could have
trouble.

maybe...
For Each ws In wb.worksheets

But I agree with the other comments about declaring your variables.

The other thing is that you don't need to refer to the worksheet using its
name. You can use that ws variable. Same with your querytable variable.

(And I didn't see a reason to activate the sheet and select A1, either.)

Option Explicit
Sub aa()
Dim wb As Workbook
Dim ws As Worksheet
Dim qt As QueryTable

'Loop through all sheets and updates all QueryTables
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
For Each qt In ws.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
Next ws
End Sub

Another alternative:

activeworkbook.refreshall

This hits the pivottables, too, if that's a problem.

 

[Buck]

Dave, how does 'activeworkbook.refreshall' sequence the refresh tasks?
More specifically, will it refresh query-tables before it refreshes the
pivot-tables based on those query tables?

 

[Dave Peterson]

I don't know, but it shouldn't take too much testing to find out.

Please post back your results when you get them.

 

[Buck]

It refreshes the sheets in the order they appear in the sheet tabs. If
Sheet1 has a pivot-table based on query-table data in sheet2 then
'Refreshall' updates the pivot-table and then the query-table, which
means changes in the query-table data aren't reflected in the
pivot-table. Looks like you would either have to move sheets with
external links to the front of the workbook or run the 'Refreshall'
twice so the pivot-table can pick up the new data on the second pass.

 

[Dave Peterson]

Thanks for posting back.

It refreshes the sheets in the order they appear in the sheet tabs. If
Sheet1 has a pivot-table based on query-table data in sheet2 then
'Refreshall' updates the pivot-table and then the query-table, which
means changes in the query-table data aren't reflected in the
pivot-table. Looks like you would either have to move sheets with
external links to the front of the workbook or run the 'Refreshall'
twice so the pivot-table can pick up the new data on the second pass.