Run Time Error 13 type Mismatch in Pivot table

[Manivannangnnavel94]

Hi,

I have created a Pivot Create Code which worked Correctly. But Now the Data i am trying to use the code is of high columns and rows.

Is there any solution on correcting this code.

which must work in larger data too.

Sub EGPivotCreate(DataSourceSheetName As String, DataSourceAddress As String, _
    DestinationSheetName As String, DestinationAddress As String, PivotName As String)
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Worksheets(DataSourceSheetName).Range(DataSourceAddress)). _
    CreatePivotTable TableDestination:=Worksheets(DestinationSheetName).Range(DestinationAddress), _
    TableName:=PivotName

End Sub

 

[RoryA]

Use the address strings instead of a range:

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & _
    DataSourceSheetName & "'!" & .Range(DataSourceAddress).Address(referencestyle:=xlR1C1))

If you can, or already do, pass the address in R1C1 format, you can use the address string directly.

 

[Manivannangnnavel94]

Hi rory,

I have been using as Variable like A1 Style Address, Since the Address Varies each time the product Changes. So How is it Possible to use the R1C1 format?

Use the address strings instead of a range:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & _
    DataSourceSheetName & "'!" & .Range(DataSourceAddress).Address(referencestyle:=xlR1C1))

If you can, or already do, pass the address in R1C1 format, you can use the address string directly.

 

[RoryA]

The code I posted will convert from A1 style to R1C1 style for you. I don't know how you are getting the initial address string at the moment, so I can't suggest how to alter that earlier.

 

[Manivannangnnavel94]

The code I posted will convert from A1 style to R1C1 style for you. I don't know how you are getting the initial address string at the moment, so I can't suggest how to alter that earlier.

I am adding an Initial address from my data sheet which Selects the address and the Address expressed a Variable and Inserted in the EGPivot Create

Example:
Call EGPivotCreate("Provision Vs actuals", Pivotaddress, Provvsactpivot, "a1", "pvapivot")

Provision Vs Actuals - DataSourceSheetName

Pivotaddress(Defined as variable) - DataSourceAddress

Provvsactpivot(defined as a variable) - DestinationSheetName

"A1"- DestinationAddress

pvapivot -PivotName


the datasource varies since each time according to the criteria provided.
This Alone need a solution.

I tried the code which you have provided above but it the code is not even moving down to the pivot codes i have provided in the VBE.

Kindly Suggest me any other option.

 

[RoryA]

I tried the code which you have provided above but it the code is not even moving down to the pivot codes i have provided in the VBE.

I'm not sure what you mean by that?

 

[Manivannangnnavel94]

Hi Rory,

Thanks for the suggestion i made some modification with the suggestion previously provided. Modified

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & _
    DataSourceSheetName & "'!" & Range(DataSourceAddress).Address(ReferenceStyle:=xlR1C1)

Now its Working Dynamically.