ziad alsayed
Well-known Member
- Joined
- Jul 17, 2010
- Messages
- 665
i am using excel 20047, below is mu code , it is working fine till i reach the code of grouping date ( highlighted in red), it give me debug ( error 1004).
appreciate any assistance
'Create Pivot Table
Set wsd = ActiveSheet
'delete any existing pivot tables
For Each pt In wst.PivotTables
pt.TableRange2.Clear
Next pt
wst.Range("I1:Z1").EntireColumn.Delete
'define the imput area of the pivot table, it means finalrow and finalcol
lastrow = wst.Cells(Rows.Count, 1).End(xlUp).Row
finalcol = wst.Cells(1, Columns.Count).End(xlToLeft).Column
'set pivot range
Set prange = wst.Cells(1, 1).Resize(lastrow, finalcol)
'setup pivot cache,it exist in memory and hold all the original data set
Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=prange.Address)
'create pivot table
Set pt = ptcache.CreatePivotTable(TableDestination:=wst.Cells(2, finalcol + 2), TableName:="Pivotziad")
'format pivot
pt.ShowTableStyleRowStripes = True
pt.TableStyle2 = "PivotStyleDark3"
'tur'n off updating while building the table
pt.ManualUpdate = True
'set up the row fields
pt.AddFields RowFields:=Array("Staff", "Date"), ColumnFields:="Data"
'set up data fields
With pt.PivotFields("Round Up")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = " Round Up"
End With
pt.NullString = "0"
pt.ManualUpdate = False
pt.ManualUpdate = True
wst.Activate
' group bonus date by month ,quater , year
pt.PivotFields(" Date").LabelRange.Group Start:=True, _
End:=True, Periods:= _
Array(False, False, False, False, True, True, True)
pt.ManualUpdate = False
pt.ManualUpdate = True
appreciate any assistance
'Create Pivot Table
Set wsd = ActiveSheet
'delete any existing pivot tables
For Each pt In wst.PivotTables
pt.TableRange2.Clear
Next pt
wst.Range("I1:Z1").EntireColumn.Delete
'define the imput area of the pivot table, it means finalrow and finalcol
lastrow = wst.Cells(Rows.Count, 1).End(xlUp).Row
finalcol = wst.Cells(1, Columns.Count).End(xlToLeft).Column
'set pivot range
Set prange = wst.Cells(1, 1).Resize(lastrow, finalcol)
'setup pivot cache,it exist in memory and hold all the original data set
Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=prange.Address)
'create pivot table
Set pt = ptcache.CreatePivotTable(TableDestination:=wst.Cells(2, finalcol + 2), TableName:="Pivotziad")
'format pivot
pt.ShowTableStyleRowStripes = True
pt.TableStyle2 = "PivotStyleDark3"
'tur'n off updating while building the table
pt.ManualUpdate = True
'set up the row fields
pt.AddFields RowFields:=Array("Staff", "Date"), ColumnFields:="Data"
'set up data fields
With pt.PivotFields("Round Up")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = " Round Up"
End With
pt.NullString = "0"
pt.ManualUpdate = False
pt.ManualUpdate = True
wst.Activate
' group bonus date by month ,quater , year
pt.PivotFields(" Date").LabelRange.Group Start:=True, _
End:=True, Periods:= _
Array(False, False, False, False, True, True, True)
pt.ManualUpdate = False
pt.ManualUpdate = True