• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create pivot table using vba

D

Dileep Reddy

Member
Hi,

I have the data in which 3 to 4 columns are text columns with more than 255 characters in one cell.
When I am trying to create the pivot cache using the following vba code. I am getting an error of Type mismatch.
but when I delete the cells which contains more than 255 characters It is working fine.

Vba code I am using to create pivotcache is
Set Pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, Range("A1:AB784"))

I am using Excel 2013


Can you please help me resolve this issue.

Thanks in advance

Regards,
Dileep
 
Dileep

Can you please post a small sample of data, maybe a dozen rows of typical data
 
Please find the attached file.

I have the problem with this four columns only because it contains cells with more than 255 characters.
When I delete the contents of the columns or delete the entire columns. my code to create pivot cache is working fine.

Thanks
Dileep
 

Attachments

  • Book1.xlsx
    37.8 KB · Views: 7
I think the issue is that the ActiveWorkbook.PivotCaches.Add method is old i.e. from a time when not as much info could be in cells.

If you record a macro while adding a pivot, you get something like this:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet3!R5C6:R16C7", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable6", DefaultVersion _
:=xlPivotTableVersion15
 
Hi Dileep ,

You are right ; when the length of text in the cells exceeds 255 , the statement fails ; I do not know whether this has changed in Excel 2010 or Excel 2013.

I am using Excel 2007 , and in this version , the statement works as long as the text length is 255 or less.

Narayan
 
Hi Dileep ,

This seems to work ; can you confirm ?
Code: 
Public Sub Create_Pivot_Table()
          Worksheets.Add
          ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Sheet1!R3C1:R444C4").CreatePivotTable TableDestination:=ActiveSheet.Range("A1"), TableName:="PivotTable6"
End Sub
Narayan
 
Hi guys,

Thanks all for the reply..but i'm still stuck at that point only.

When you hardcode the source data it is working fine with Add method or Create method. But I want to have the source data to be dynamic range.

I am unable to create the pivot table.

Please refer to the below code which I am using

Public Sub Create_Pivot_Table()
Lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A3:D" & Lastrow)
Worksheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng).CreatePivotTable TableDestination:=ActiveSheet.Range("A1"), TableName:="PivotTable6"
End Sub


Can any one help me solve this issue.

Thanks again

Dileep
 
Hi Narayan,
Thanks for the code.
But what if I want the sheet name also be dynamic

I tried with the following code. But it didn't work

Public Sub Create_Pivot_Table()
Worksheets("Sheet1").Activate
sht = ActiveSheet.Name
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A3:D" & Lastrow)

Worksheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sht & "!" & Rng.Address).CreatePivotTable TableDestination:=ActiveSheet.Range("A1")
End Sub

Thanks
Dileep
 
Hi Narayan,

My sincere apologies.

Finally I want to build a pivot table with a sheet name and source data variables but I could not do this because of columns with more than 255 characters.

Thanks again for your patience

Dileep
 
Hi Dileep ,

No need to apologise ; I am sorry. The point is that if all the requirements are known in advance , probably some effort / time can be saved.

See this file.

Narayan
 

Attachments

  • Book15.xlsm
    56.4 KB · Views: 43
Dileep

It is really important that you give us as much information up front as is possible.

This is because although you are trying to do something, there may actually be better or other ways to solve your problem.

A sample file also remove the errors associated with assuming what your data is

Have a read of the post: New Users Start Here at: http://chandoo.org/forum/forums/new-users-please-start-here.14/
 
You must log in or register to reply here.
Back
Top