PDA

View Full Version : How to create a dynamic pivot table using macro in excel



bunty
01-23-2015, 11:40 PM
Hi All,

Could you please guide me how to write a macro to create a dynamic pivot table.
Below the example:

Sl. No Name Open Date Closed date Status

1 Ravi 21/12/2014 31/12/2014 closed

2 ABC 23/01/2015 WIP

3 QWE 13/01/2015 WIP

4 XYZ 01/01/2015 20/01/2015 closed


The pivot will be name will come as a group like Ravi, OWE and XYZ will be in one team (Example:Team A) and ABC will be in team B.To display the team in place of name, how we can write a code.I don't have much experience with vba code and looking for your help to learn this.

Thanks for the help...
Bunty..

Simon Lloyd
01-26-2015, 07:42 AM
This should explain how to create a Pivot Table http://www.excel-easy.com/data-analysis/pivot-tables.html we cant help you because we cant see your data structure or ranges, besides why would you need to use VBA to create a Pivot Table?

bunty
01-26-2015, 12:03 PM
Hi Simon,

Thanks for your response.I wanted to learn VBA and automate this task where the data keep changing on a daily basis.Please find below the code where I am getting run time error 1004 (Application-define or object defined error).

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




Please let me know how I can resolve this error and display the team name in place of individual name in pivot table.

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

Thanks....

bunty
01-26-2015, 12:07 PM
Hi Simon,

Please find the sample data.which keep changing.

Thanks..

Navdeep K
05-03-2016, 12:20 AM
Hi,

Using excel 2010, getting runtime error 5 invalid procedure call or argument even in the sample file

Simon Lloyd
06-23-2016, 12:32 PM
Runtime error 5 is where a sheet name doesn't exist or has a typo.

mdmackillop
06-23-2016, 02:15 PM
Running your code on your posted sample, change these 2 lines to the correct values.

Set Pivot_sht = ThisWorkbook.Worksheets("Sheet5")
PivotName = "PivotTable3"