Refreshing Tables with VBA
<p>I have two tables, one with a list of cities( we'll call this City List), and another with data points that correspond with those cities ( Call this The Data Table). The Data Table, is connected to a Select query that I built in MS SQL Server. This Select query/ Data Table has a single <code>Where</code> clause in which I have substituted the SQL criteria and replaced a <code>?</code> in order to make it a parameter when connected to Excel. </p>

<p>Now that I have that out of the way, I'll explain what I'm trying to accomplish. I want to loop through the City List and for each city in the list, update The Data Table to reflect the data points for the city. Ultimately, I would like to loop through and each time The Data Table is refreshed, it saves a copy of the workbook with that specific table.</p>

<p>I have posted my current code down below, but my issue is that the table never refreshes with the current data that corresponds with the city that is currently selected via the loop. With that being said, if I hit the <code>escape</code> key to break out of the VBA macro, the table will then refresh with whatever the last city was before I stopped the macro. </p>

<p>Code:</p>

<pre><code>Sub Macro1()
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim CT As Variant
Dim MSG As String

Set WS = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
CT = Range("A1").CurrentRegion
For i = 2 To UBound(CT, 1)
    MSG = ""
    For J = 1 To UBound(CT, 2)
        WS.Range("$D$2").Value = CT(i, J) //Places the city into Cell $D$2 which is where The Data Table looks to for the parameter.

    Exit For


    Next J

        ActiveWorkbook.Connections("Query from Database").Refresh

          WS2.ListObjects(1).Refresh

Next i
End Sub
</code></pre>

<p>It's almost as though the macro is running too fast for the table to catch up and refresh. I've tried adding some wait times into the code, in hopes that it would give it enough time to allow the table to refresh, but that had no affect. I have also turned off <code>Background Refresh</code>, and that doesn't seem to do anything either. Right now it just loops through the city table, and with each city it shows that the query is refreshing, but after the query is finished refreshing, it goes onto the next city without ever updating The Data Table. HELP!</p>