Run time error while trying to change Pivot Table range

[ThrottleWorks]

Hi,

I am using below mentioned code to change range of Pivot table.
This code is working fine at my machine, when tried at another machine code is giving bug.

Worksheet name, pivot table name is same on both the files, range is valid, still it is giving error as object type error. I am not able to understand the reason.

What should I check further to identify this issue.

Can anyone please help me in this.

Sht6.PivotTables("PivotTable7").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= rng10, Version:=xlPivotTableVersion10)

 

[Luke M]

Double-check:
Both machines running exact same file?
Both using exact same version of Excel?
Sht6 is either a sheet code name, or a define variable?
rng10 is a defined range? (has column headers, no blank column headers)

As for code itself, only thing I might change is the ActiveWorkbook object. In the rare case that the pivot table you're working with is not in the active workbook, let's change that to ThisWorkbook

Sht6.PivotTables("PivotTable7").ChangePivotCache ThisWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= rng10, Version:=xlPivotTableVersion10)

 

[Chihiro]

Is your range source Table or defined cell range?

Sometimes what happens is that path to the file is included in the source. That will cause object type error, especially if your source is named Table.

 

[ThrottleWorks]

Hi @Luke M sir, and @Chihiro sir, thanks a lot for the help. Sorry for late reply.
I am checking all the points as advised by you, will revert with details.

Have a nice day ahead.

 

[ThrottleWorks]

Hi,

This is the same issues I faced earlier, as mentioned in below link.

http://forum.chandoo.org/threads/run-time-error-while-refreshing-pivot-table.25400/

My coding is not allowing range beyond 65,536 rows. I am trying to change the code. Will revert with details. Thanks.

 

[ThrottleWorks]

Hi,

I have copied and edited code from below mentioned web site and it seems to be working fine. Thanks.

http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range

 

[shrivallabha]

These limits that you are indicating:
256 Columns, 65536 Rows
mean that you are using *.xls (2003 Version) extension file in versions 2007 and higher.

Since you cannot post the workbook check where you have used these settings.
You can use Rows.Count and Columns.Count options to get around hard coding.

Edit: Or you can choose to save it in higher version if it is OK and then verify if issue goes away.