Code error while creating dynamic pivot table

[bunty]

Hi,

Please help me to create a dynamic pivot table.While running this code I am getting error "One of your data columns has a blank heading".But all the column has headings.Please find the below code with sample set of data.

Sub AdjustPivotDataRange()Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String


  Set Data_sht = ThisWorkbook.Worksheets("Sheet1")
  Set Pivot_sht = ThisWorkbook.Worksheets("Sheet2")




  PivotName = "PivotTable1"


  Set StartPoint = Data_sht.Range("A1")
  Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
  
  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)


  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If


  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)


  Pivot_sht.PivotTables(PivotName).RefreshTable


  MsgBox PivotName & "'s data source range has been successfully updated!"


End Sub

Data:

SL#	Name	Open Date	Closed Date	Status
123	Paul	21/11/2014 <tbody> </tbody>	1/1/2015 <tbody> </tbody>	Closed
435	Sidd	20/01/2015 <tbody> </tbody>		WIP
2rt	Rajiv	11/11/2014 <tbody> </tbody>	14/01/2015 <tbody> </tbody>	Closed
354	Sidd	31/12/2014 <tbody> </tbody>		WIP
657	Paul	22/01/2015 <tbody> </tbody>		WIP
786	Maddy	13/09/2014 <tbody> </tbody>	21/01/2015 <tbody> </tbody>	Closed
536	Sunny	22/01/2015 <tbody> </tbody>
549	Rohit	23/01/2015 <tbody> </tbody>
864	Anya	25/01/2015 <tbody> </tbody>

<tbody>
</tbody>

Could you please help me to resolve this issue and how i can group the name in a group (example:In pivot chart name should replace with some group name like Ayna,Paul,Rajiv in one GroupA and Rohit.Sidd in GroupB and rest in GroupC).So in place of name team name should display in Pivot table.

Thanks you all and Kindly help to resolve this issue...

 

[Andrew Poulsom]

VBA won't be lying. Does this return what you would expect?

MsgBox Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

Maybe you can use:

Set DataRange = StartPoint.CurrentRegion

 

[bunty]

Hi Andrew,

Thanks for a quick response..

I tried that but its giving error "Application defined or object defined error".


Thanks...

 

[Andrew Poulsom]

What is giving that error exactly?

 

[bunty]

It's a run time error 1004 (Application-define or object defined error in below script

Pivot_sht.PivotTables(PivotName).ChangePivotCache _            ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

 

[Andrew Poulsom]

That doesn't look at all like the code I posted.

 

[bunty]

Hi Andrew,

This is the part of same code which I share and made a changes as you suggested.After this change while running the code I was getting this run time error.
Please find below the code:

Dim Pivot_sht As WorksheetDim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String


  Set Data_sht = ThisWorkbook.Worksheets("Sheet1")
  Set Pivot_sht = ThisWorkbook.Worksheets("Sheet2")




  PivotName = "PivotTable1"


  Set StartPoint = Data_sht.Range("A1")
  'Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
  Set DataRange = StartPoint.CurrentRegion
  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)


  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If


  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)


  Pivot_sht.PivotTables(PivotName).RefreshTable


  MsgBox PivotName & "'s data source range has been successfully updated!"


End Sub

Once again thanks for your response and looking for your support..

 

[Andrew Poulsom]

Does the data you posted start in A1? What's to the right of it?

 

[bunty]

Hi,

A1 contain header of a column (example:SL#)

Thanks,

 

[Andrew Poulsom]

What's to the right of the data that you posted (column F onwards)?