CODEWITHSUNDEEP
vbaexcelpivot
Selrac
Selrac

Reputation: 2293

Excel VBA change data souce based on reference value

I can not manage to figure out how to change the data source of pivot table with VBA.

I have the data source in Sheet1 and the pivot in Sheet 2

Sheets("Sheet1").Select 
LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.count - 1, 0).End(xlUp).Row
sNewSource = ActiveSheet.Name & "!" & Range("A19:X" & LastRow).Address(ReferenceStyle:=xlR1C1)

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache _
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
  SourceData:=sNewSource, Version:=xlPivotTableVersion14)

At the moment I'm getting an error message: Unable to get the PivotTables properly of the Worksheet class

I tried looking at other solutions, but I can not figure out where it is not working.

Any ideas?

Upvotes: 0

Views: 273

Answers (3)

CallumDA
CallumDA

Reputation: 12113

This is not a direct answer to your question, more an opportunity to explain how it could possibly be done better.

All of these options involve creating a named range or table name, which you would use as the data source for your pivot table

Option 1

Give your data a named range and get VBA to update that named range

With ThisWorkbook.Names.Item("myRange")
    .RefersTo = Range(.RefersToRange, .RefersToRange.End(xlDown))
End With

Option 2

Give your data a dynamic named range. The source reference might look like this:

=OFFSET(A1,,,COUNTA(A:A),COUNTA(1:1))

Note, OFFSET is a volatile function and will slow down bigger workbooks

Option 3

Put your data into a Data Table. When you format your data as a Table excel will automatically resize when you add new data below

Upvotes: 1

Selrac
Selrac

Reputation: 2293

Thanks for all the advice. In the end I managed to make it work with the following code:

Sheets("Sheet1").Select ' Data Source
LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
sNewSource = ActiveSheet.Name & "!" & Range("A1:B" & LastRow).Address(ReferenceStyle:=xlR1C1)
sNewSource = "C:\xxx\[test.xlsm]" & sNewSource

Sheets("Sheet2").Select ' Pivot Table
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sNewSource, Version:= _
        xlPivotTableVersion14)

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Problems often arise when using Select and ActiveSheet. You have tried to access a PivotTable on Sheet1 when there are likely no PivotTables on Sheet1 (as you said, your PivotTable is on Sheet2).

This can be addressed by fully qualifying your references -- that means being explicit about which workbook and which worksheet you wish to use.

With that in mind, does this work? (untested)

With ThisWorkbook.Worksheets("Sheet1")
    LastRow = .Range("A1").Offset(.Rows.count - 1, 0).End(xlUp).Row
    sNewSource = .Name & "!" & .Range("A19:X" & LastRow).Address(ReferenceStyle:=xlR1C1)
End With

With ThisWorkbook.Worksheets("Sheet2")
    .PivotTables("PivotTable1").ChangePivotCache .PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sNewSource, Version:=xlPivotTableVersion14)
End With

Upvotes: 0

Related Questions