Refreshing a pivot table with a macro

[thundardog]

I am importing a file, then attempting to refresh the pivot table that references this imported data. The problem is that when I "Refresh", even when doing without the macro, the Data Source has shifted, even using absolute referencing. How can I get the data source to stay in the location I want and refresh with a macro?

Any ideas?

 

[Andrew Poulsom]

What's causing the Data Source to shift and in what way is it shifting?

 

[thundardog]

the data source is set as follows "$A$1:$AJ$65536", after I import the data from a .csv file it shifts left to "$AK$1:$BT$65536" or beyond. Each time it will shift to the left a multiple of 36 colums, i believe dependent on how many times I run the macro.

 

[Andrew Poulsom]

Can we see your macro please?

 

[thundardog]

Below is the macro as it was created, I am using it as a part of a sub

Sub RefreshPivotTable()
'
' RefreshPivotTableTest Macro
'
'
Sheets("Keyops Data Dump").Select
Range("A1").Select
Sheets("Pivot").Select
Range("B140").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("B1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

 

[Andrew Poulsom]

No, the macro that imports the data.

 

[thundardog]

Sorry, here it is

Sub ImportFile()
'
' ImportFile Macro
'
'
Sheets("Keyops Data Dump").Select
Range("A1").Select
' With ActiveSheet.QueryTables.Add(Connection:= _
' "TEXT;S:\GENERAL\Tioga FMS Revenue\output.csv", Destination:=Range("$A$1"))
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Danny Schall\Revenue\output.csv", Destination:=Range("$A$1"))
.Name = "output"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Keyops Data Dump").Select
Range("A1").Select
End Sub

 

[Andrew Poulsom]

Why don't you just refresh the existing QueryTable or delete the existing ones and clear all the cells on the worksheet before adding the new one?

 

[thundardog]

The pivot table is set up to pull data out of for a table/form. So, the pivot table was setup for this purpose, so I would like to keep the pivot table as it was given to me.

 

[Andrew Poulsom]

I haven't mentioned the pivot table, only the QueryTable.