I have the following code below which works perfectly to update the Pivot tables
I would like to amend the section below so that instead of having to type the name each time the workbook is saved using another name, the active workbook (being this workbook) is displayed over here using code to extract the name from the workbook
I have attempted to do this but cannot get it to work
It would be appreciated if someone could assist me
Code:
Sub Update_Pivot()
Sheets("Pivot Table").Select
ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\My Documents\[Mens Dept Sales.xlsm]Imported Data!C1:C19" _
, Version:=xlPivotTableVersion14)
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Ageing")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Outstanding"), "Count of Outstanding", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Outstanding")
.Caption = "Sum of Outstanding"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable4").PivotSelect "Ageing[All]", xlLabelOnly _
+ xlFirstRow, True
Selection.Group Start:=30, End:=120, By:=30
ActiveSheet.PivotTables("PivotTable4").PivotSelect "Ageing[All]", xlLabelOnly _
+ xlFirstRow, True
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.PivotItems("(blank)").Visible = False
End With
Range("A5").Select
With Range("A:E").EntireColumn.AutoFit
End With
End Sub
I would like to amend the section below so that instead of having to type the name each time the workbook is saved using another name, the active workbook (being this workbook) is displayed over here using code to extract the name from the workbook
Code:
"C:\My Documents\[Mens Dept Sales.xlsm]Imported Data!C1:C19" _
I have attempted to do this but cannot get it to work
It would be appreciated if someone could assist me
Code:
Sub Update_Pivot()
Sheets("Pivot Table").Select
Dim wkb As Workbook
ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Set wkb = activeworkbook.Sheets(Imported Data!C1:C19") _
, Version:=xlPivotTableVersion14)
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Ageing")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Outstanding"), "Count of Outstanding", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Outstanding")
.Caption = "Sum of Outstanding"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable4").PivotSelect "Ageing[All]", xlLabelOnly _
+ xlFirstRow, True
Selection.Group Start:=30, End:=120, By:=30
ActiveSheet.PivotTables("PivotTable4").PivotSelect "Ageing[All]", xlLabelOnly _
+ xlFirstRow, True
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
.PivotItems("(blank)").Visible = False
End With
Range("A5").Select
With Range("A:E").EntireColumn.AutoFit
End With
End Sub