Data range in Pivot Table with VBA statement

[Hans810]

Good evening all,

I have the following situation:
From a data base I retrieve data (with sql statements) which I convert into a pivot table. This proces I do in a vba programme in a constant flow (data from different suppliers of goods to our company).
The output is a number of Excel files (same number as suppliers).
However, each file contains a different range of data, ie number of lines.
Which VBA command can I use to tell the pivot table what the data range is???

If I do this in each file by hand I use (with my cursor in a data cell of the pivot table) "Options", and then "other data source", in order to catch all the basic data for the pivot table.

Please help!!

Thanks in advance from a cold Holland

 

[Jerry Sullivan]

Thanks in advance from a cold Holland

Hello Hans,

You're welcome to come warm up with a visit to Sunny San Diego!

You can change the PivotTable SourceData with VBA using something like this...

Sub UpdateDataSource()
    Sheets("Pivots").PivotTables("PivotTable1").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=Sheets("MyData").Range("A1").CurrentRegion)
End Sub

As an alternative, you might want to explore using a Dynamic Named Range as your datasource.

 

[Hans810]

Hello JS,

Thank you very much for the solution. I got it working finally.
Some things I have altered to get it working.
Here is my code:

start of the code

Sheets("DATA").Select

GG = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Draaitabel").Activate

ActiveSheet.PivotTables("Draaitabel1").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=Sheets("DATA").Range("A5:F" & GG))

end of the code

For chilling down you are welcome in Holland!
Best regards, Hans