Pivot Table code

[IThelp]

'+----------------------------+
'|  SALES (£) BY DAY BY WEEK  |
'+----------------------------+
Sub salesByDayByWeek_PivotFields()
Dim weekNum As Integer


On Error Resume Next

Sheets("SalesDBPivot").Select
Dim pvt As PivotTable
Dim pf As PivotField

Set pvt = ActiveSheet.PivotTables("PivotTable1")
pvt.ClearTable
    
'Turn on Automatic updates/calculations --like screenupdating to speed up code
    pvt.ManualUpdate = True
    
'Add item to the Report Filter (xlPageField / "Filter")
    
    With pvt.PivotFields("WeekNo")
        .Orientation = xlPageField
        .Position = 1
    End With
  
'Add item to the Column Labels (xlColumnField / "Columns")
    With pvt.PivotFields("DayOfWeek")
        .Orientation = xlColumnField
    End With


    With pvt.PivotFields("DayOfWeek")
        .PivotItems("Saturday").Visible = False
        .PivotItems("Sunday").Visible = False
    End With
    
'Add item to the Row Labels (xlRowField / "Rows")
    With pvt.PivotFields("Item Name For Lookup")
        .Orientation = xlRowField
        .Position = 1
    End With
    
'Add Calculated Field, which is adding item to Values (xlCount / "Values") xlCount could be sum etc
    For Each pf In pvt.PivotFields
       If pf.SourceName = "TotalPrice" Then
          Exit For
       End If
     Next
  pvt.AddDataField pf, "Sum of Total Price", xlSum
  

'Position Item in list
    'pvt.PivotFields("xxx").Position = 1
    
'Format Pivot Field for formatting data
    pvt.PivotFields("Sum of Total Price").NumberFormat = "£#,##0.00"

'Set xxxx Filter
curWeek = Year(Now()) & "-" & (Format(Now(), "ww") + weekNum)

'MsgBox (curWeek)
'curMonth = MonthName(Month(Now()))
    ActiveSheet.PivotTables("PivotTable1").PivotFields("WeekNo").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("WeekNo").CurrentPage = curWeek

pvt.ManualUpdate = False
pvt.RefreshTable

LastCol = Range(pvt.TableRange1.Address).Columns.Count + Range(pvt.DataLabelRange.Address).Column - 2
lastrow = Range(pvt.TableRange1.Address).Rows.Count
pvt.PivotFields("Item Name For Lookup").AutoSort xlDescending, "Sum of Quantity", pvt.PivotColumnAxis.PivotLines(LastCol - 1), 1
'pvt.PivotFields("Item Name For Lookup").AutoSort xlDescending, "Sum of Quantity", pvt.PivotColumnAxis.PivotLines(3), 1
    
End Sub

Hi,

I have used this vba code to update a pivot table which contains sales data.

when the pivot table updates, it contains the right products from the sales sheet, However, it for the price, it comes up as 0.
On the pivot field list, the total price is put as the value, when it is put as a row label, it comes up with the correct price. The total price needs to come as the value.

If there is any way you could help it would be much appreciated.

Many thanks

 

[mole999]

Your repeated post has been deleted. please do not create duplicate posts