Hi Team,
I am Creating a Pivot Table, Below code works if there is no space in Sheet Name of Destination worksheets.
Destination Sheet Name Greaterthan30Days Code works here.
if space in Destination Sheet Name Greater than 30 Days Pivot will not created.
ws_Pivot.Name = "Greaterthan30Days" ' If no space in sheet Name Pivot get Created.
rng_destination = ws_Pivot.Name & "!" & ws_Pivot.Range("A3").Address(, , xlR1C1)
ws_Pivot.Name = "Greater than 30 Days" ' If space in sheet Name Pivot will not get Created.
rng_destination = ws_Pivot.Name & "!" & ws_Pivot.Range("A3").Address(, , xlR1C1)
Below is attempted code.
Thanks
mg
I am Creating a Pivot Table, Below code works if there is no space in Sheet Name of Destination worksheets.
Destination Sheet Name Greaterthan30Days Code works here.
if space in Destination Sheet Name Greater than 30 Days Pivot will not created.
ws_Pivot.Name = "Greaterthan30Days" ' If no space in sheet Name Pivot get Created.
rng_destination = ws_Pivot.Name & "!" & ws_Pivot.Range("A3").Address(, , xlR1C1)
ws_Pivot.Name = "Greater than 30 Days" ' If space in sheet Name Pivot will not get Created.
rng_destination = ws_Pivot.Name & "!" & ws_Pivot.Range("A3").Address(, , xlR1C1)
Below is attempted code.
VBA Code:
Sub CreatePivot(ByVal wb As Workbook, sht As Worksheet, Optional ByVal pname As String)
Dim LastCol As Long
Dim LatRow As Long
Dim rng_source As Range
Dim NewRange As String
Dim pt As PivotTable
Dim pc As PivotCache
Dim pf As PivotField
Dim rng_destination As String
Dim prng As String
Dim ptName As String
Dim ws_Pivot As Worksheet
Set ws_Pivot = wb.Worksheets.Add
ws_Pivot.Name = "Greaterthan30Days" ' If no space in sheet Name Pivot get Created.
ws_Pivot.Name = "Greater than 30 Days" ' If space in sheet Name Pivot will not get Created.
With sht
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
LatRow = .Cells(.Rows.Count, 4).End(xlUp).row
Set rng_source = .Range(.Cells(2, 1), .Cells(LatRow, LastCol))
prng = sht.Name & "!" & rng_source.Address(, , xlR1C1)
End With
'
rng_destination = ws_Pivot.Name & "!" & ws_Pivot.Range("A3").Address(, , xlR1C1)
'Create Pivot
Set pc = wb.PivotCaches.Create(xlDatabase, prng)
Set pt = pc.CreatePivotTable(rng_destination, pname)
'Add item to the Report Filter
pt.PivotFields("Region").Orientation = xlRowField
pt.PivotFields("Ageing Bucket").Orientation = xlColumnField
With pt
Set pf = .PivotFields("Outstanding Amount")
With pf
.Orientation = xlDataField
.Function = xlSum
End With
End With
Set pf = pt.PivotFields("Region")
pf.AutoSort xlDescending, "Sum of Outstanding Amount"
Dim pf_Remark As PivotField
Set pf_Remark = pt.PivotFields("Ageing Bucket")
Application.AddCustomList Array(">1 Year", "121-365 Days", "91-120 Days", "61-90 Days", "31-60 Days", "16-30 Days", "8-15 Days", "0-7 Days")
pf_Remark.AutoSort Order:=1, Field:="Ageing Bucket"
sht.PivotTables(ptName).RefreshTable
End Sub
Thanks
mg