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

[Mallesh23]

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.

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

 

[RoryA]

You need to put an apostrophe before and after the sheet name, like this:

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

 

[Mallesh23]

Hi Rory,

Perfect ! it worked, Thanks a lot for your help, you saved lot of my time. ?


Thanks
mg

 

[Jon Peltier]

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]

Hi Jon,

Thanks for the information. will use henceforth. ?


Thanks
mg