vba help - Issue in Creating Pivot if space in sheet Name

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
820
Office Version
  1. 2010
Platform
  1. Windows
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.

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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,811
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to put an apostrophe before and after the sheet name, like this:

Code:
prng = "'" & sht.Name & "'!" & rng_source.Address(, , xlR1C1)
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
820
Office Version
  1. 2010
Platform
  1. Windows
Hi Rory,

Perfect ! it worked, Thanks a lot for your help, you saved lot of my time. (y) 🕺


Thanks
mg
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,931
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hint - Use Rory's approach of wrapping the sheet name in single quotes all the time. If you need the single quotes, they are there. If you don't, Excel just ignores them.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
820
Office Version
  1. 2010
Platform
  1. Windows
Hi Jon,

Thanks for the information. will use henceforth. (y) 🕺


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,118,309
Messages
5,571,468
Members
412,395
Latest member
nielsvanlit
Top