Pivot Table

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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,123,054
Messages
5,599,533
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top