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

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You need to put an apostrophe before and after the sheet name, like this:

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

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


Thanks
mg
 
Upvote 0
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.
 
Upvote 0
Hi Jon,

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


Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top