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

Mallesh23

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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,819
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
968
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
5,172
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
968
Office Version
  1. 2010
Platform
  1. Windows
Hi Jon,

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


Thanks
mg
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,551
Messages
5,838,055
Members
430,527
Latest member
MyFace2

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
Top