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...

 

[bunty]

Hi,

It's Blank and I was able to create pivot table.But not sure how to group the column labels.
Example:

Status	Anya	FSE	Maddy	Paul	Rajiv	Rohit	Sidd	Grand Total
Closed	1	1	1		1		2	6
WIP	1			1		1	2	5
Grand Total	2	1	1	1	1	1	4	11

<tbody>
</tbody>

I am trying to group these name column in different groups like Anya/Maddy will be in Team A, FSE/Paul will be in team B and others in Team c.
Please find the below code and if possible please help me to implement it..

Sub CreatePivot()    ' Creates a PivotTable report from the table on Sheet1
    ' by using the PivotTableWizard method with the PivotFields
    ' method to specify the fields in the PivotTable.
    Dim objTable As PivotTable, objField As PivotField
    
    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("Sheet1").Select
    Range("A1").Select
    
    ' Create the PivotTable object based on the Employee data on Sheet1.
    Set objTable = Sheet1.PivotTableWizard
    
    ' Specify row and column fields.
    Set objField = objTable.PivotFields("STATUS")
    objField.Orientation = xlRowField
    Set objField = objTable.PivotFields("Name")
    objField.Orientation = xlColumnField
    
    
    ' Specify a data field with its summary
    ' function and format.
    Set objField = objTable.PivotFields("SL#")
    objField.Orientation = xlDataField
    objField.Function = xlCount
    
    
         
    ' Preview the new PivotTable report.
    ActiveSheet.PrintPreview
    
    ' Prompt the user whether to delete the PivotTable.
    Application.DisplayAlerts = False
    If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True
    
End Sub

 

[Andrew Poulsom]

That's not at all like the data in your original post. To create a pivot table your data should ideally be in first normal form, ie columns for Status, Name and Value and a row for each combination.

 

[bunty]

Hi,

It's a pivot table which got created after the code change which I shared in last post.Data set is same, may be one or two records are more.
As per your last post I understood that we can not combine these in a group based on a name column.Here we have a fix set of name which we can hard code also to create a group but not sure how.
I was trying to attach my file but not able to find the attachment option
one more question in this code whenever I am running it's creating a pivot in a new sheet.Is there any way to to create a pivot always in a particular sheet like sheet 2 ?

Thanks,

 

[Andrew Poulsom]

Why don't you add a column for Team to your source data and put that in the pivot table? Also, if you already have a pivot table why don't you refresh it rather than creating a new one?

As an aside please stop putting your questions in QUOTE tags as it's very confusing (at least to me).

 

[bunty]

The source data which I am getting on daily basis does not have team column and that's why I wanted to create group based on a name column because name contain many records and if we go with only name then pivot table size will be keep increasing and does not look good..
Regarding pivot yes even I wanted to refresh the same pivot but no idea on it.

Please help me if you have any idea on this.

Thanks a lot...

 

[Andrew Poulsom]

Can't you add a Team column using a lookup formula from a two column list? If you convert your data to a Table you can create a pivot table from it refresh its pivot cache in code (or manually).

 

[bunty]

Yes,you are correct we can use vlookup but I don't know how to implement it in vba and how to refresh the cache.
If you don't mind can you please give some hint for writing a lookup and refresh the pivot cache.

Thanks,

 

[Andrew Poulsom]

The macro recorder should give you some starter code for that.