Refreshing specific tables

kris21

New member
Joined
Jul 2, 2016
Messages
24
Reaction score
0
Points
0
Hi Gurus,

I am trying to refresh individually different tables(Data connections from share point) in my workbook. I don't want to use Refresh all as it will affect the pivot tables.

This is the code I got using macro recorder.

Code:
Private Sub RefreshTables()'
Application.ScreenUpdating = False


    Sheets("Trend").Activate
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("Plan").Activate
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("Finance").Activate
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("Architecture").Activate
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("MCP").Activate
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Sheets("Program").Activate
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    
End Sub


  1. Am getting an Run-time error "91': Object variable or With block variable not set error.
  2. How can I clean this code up?


Can some one guide me on this please?
 
Completely untested, and off the top of my head, a variety of ways; if there's only one refreshable table on a sheet then something like the first 2 refresh lines below, the 3rd line uses the name of the table.
Code:
Private Sub RefreshTables()  '
Application.ScreenUpdating = False

Sheets("Trend").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
Sheets("Plan").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
Sheets("Finance").ListObjects("TheNameOfTheTable-AdjustThis").QueryTable.Refresh BackgroundQuery:=False

Application.ScreenUpdating = True
End Sub
 
Back
Top