Pivot Table Refresh Data Issue

[Nlreid]

Hi all,

New to VBA and I'm basically trying to correct a broken macro. It pulls and refreshes data from external sources into different pivot tables (one for each of the 10 sources). It works great except ONE of the pivot tables references a completely different source.

The table I'm having trouble with pulls from a sheet called "DF_KEYS" and I've created a named range "DF_KEYS_DR" with the logic: =OFFSET(DF_KEYS!$A$1,0,0,COUNTA(DF_KEYS!$A:$A),19), so it has a dynamic number of rows.

After the macro is refreshed though, it references a range off of a different sheet. Is there any logic in here that would make the range change?

I highlighted what I think it could be, but I'm not having any luck changing it.

Thanks in advance!

Sub Refresh_Click()
' Sub: Refresh_Click -- executed on click of the Refresh button
' Created: 12/20/2010
' Modified:
'
On Error GoTo Refresh_Click_Err
Dim wkbk As Workbook, wkshtHome As Worksheet, wkshtTarget As Worksheet
Dim strSourceDir As String, strFileIn As String, strPivotName As String, strTargetSheet As String
Dim intLoadFile As Integer, wkshtPivot As Worksheet
Dim intNumFiles As Integer, i As Integer, intUpperBound As Integer
Dim strSecondPivotName As String, strThirdPivotName As String
Dim rg As Range, varTableRange As Variant
Dim varPivotSource As Variant
'This constant defines the upper row for the table listing of files to process
Const FILE_REF_ROW = 7
Set wkbk = ActiveWorkbook
Set wkshtHome = wkbk.Worksheets(HOME_WORKSHEET)
intNumFiles = wkshtHome.Cells(1, 9).Value
If intNumFiles <= 0 Then
MsgBox "Number of files variable in cell 'I1' must be a number greater than zero."
GoTo Refresh_Click_Exit
Else
'define the upper bound of the processing table
intUpperBound = FILE_REF_ROW + (intNumFiles - 1)
End If
i = FILE_REF_ROW
'Source Directory field is hard-coded to cell 'D4'
strSourceDir = wkshtHome.Cells(4, 4).Value
'Loop through and process all tables
While i <= intUpperBound

strFileIn = wkshtHome.Cells(i, 7).Value
strTargetSheet = wkshtHome.Cells(i, 8).Value
strPivotName = wkshtHome.Cells(i, 9).Value
strSecondPivotName = wkshtHome.Cells(i, 10).Value
strThirdPivotName = wkshtHome.Cells(i, 11).Value
'Call the function to get the file data and populate to the sheet
intLoadFile = intLoadData(strSourceDir, strFileIn, strTargetSheet)
'MsgBox strTargetSheet & " refresh result is: " & intLoadFile

If intLoadFile Then
'load was successful
'find the new dynamic data range for loaded data and use to reset Pivot Table data source references
Worksheets(strTargetSheet).Activate
Set rg = ActiveSheet.UsedRange
varTableRange = rg.Address(ReferenceStyle:=xlR1C1)
varPivotSource = strTargetSheet & "!" & varTableRange

'refresh pivot table
Set wkshtPivot = wkbk.Worksheets(strPivotName)
'assume no more than 1 pivot table per report page, can cheat with reference "1" here
'reset pivot source to the value determined above
wkshtPivot.PivotTables(1).SourceData = varPivotSource
'refresh pivot
wkshtPivot.PivotTables(1).RefreshTable

'if there is a second pivot associated with this data tab then refresh it also
If strSecondPivotName <> "" Then
'refresh pivot table
Set wkshtPivot = wkbk.Worksheets(strSecondPivotName)
wkshtPivot.PivotTables(1).SourceData = varPivotSource
'assume no more than 1 pivot table per report page, can cheat with reference "1" here
wkshtPivot.PivotTables(1).RefreshTable
End If

'if there is a third pivot associated with this data tab then refresh it also
If strThirdPivotName <> "" Then
'refresh pivot table
Set wkshtPivot = wkbk.Worksheets(strThirdPivotName)
wkshtPivot.PivotTables(1).SourceData = varPivotSource
'assume no more than 1 pivot table per report page, can cheat with reference "1" here
wkshtPivot.PivotTables(1).RefreshTable
End If


Else
'do nothing
End If
'Loop to next file
i = i + 1
Wend
Refresh_Click_Exit:
Exit Sub
Refresh_Click_Err:
MsgBox "Refresh_Click Error: " & Err.Number & ": " & Err.Description
Resume Refresh_Click_Exit
End Sub