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.
Data:
<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...
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.
Code:
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 |
<tbody> </tbody> |
<tbody> </tbody> | Closed | ||
435 | Sidd |
<tbody> </tbody> | WIP | |||
2rt | Rajiv |
<tbody> </tbody> |
<tbody> </tbody> | Closed | ||
354 | Sidd |
<tbody> </tbody> | WIP | |||
657 | Paul |
<tbody> </tbody> | WIP | |||
786 | Maddy |
<tbody> </tbody> |
<tbody> </tbody> | Closed | ||
536 | Sunny |
<tbody> </tbody> | ||||
549 | Rohit |
<tbody> </tbody> | ||||
864 | Anya |
<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...