Issue creating pivot table from table data

[TenDeadGoats]

Hi guys,

I almost have this piece of code working exactly as I want it to. My only issue is that I want the pivot table to be created on an existing worksheet called "Client Distribution" instead of the macro creating a new worksheet to put the pivot table in. Does anyone know how I can fix this? When I try to change the destination worksheet (you can see it commented out in the code) it gives me an error on the very last line of code. I would really appreciate any help!

Sub Table_Insert()    
    
Dim LastColumn As Integer
Dim LastRow As Integer
Dim FitRange As Range


    ActiveSheet.ListObjects("Table1").ListColumns.Add
    
    LastColumn = ActiveSheet.ListObjects("Table1").Range.Columns.Count
    LastRow = ActiveSheet.ListObjects("Table1").Range.Rows.Count
    
    ActiveSheet.ListObjects("Table1").HeaderRowRange(LastColumn).Select
    
    ActiveCell.FormulaR1C1 = "CLIENT NAME"
    
    ActiveCell.Offset(1, 0).Select
    
    ActiveCell.FormulaR1C1 = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
    
    ActiveSheet.ListObjects("Table1").ListColumns(LastColumn).Range.Select
    
    Set FitRange = Selection
    
    'Worksheets("Commissions Data").Range(FitRange).Columns.AutoFit
    
    
    
    
    
    
    
    
    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
 
    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String
 
    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable
 
    'identify source and destination worksheets. Add destination worksheet
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Commissions Data")
        'Set myDestinationWorksheet = .Worksheets("Client Distribution")
        Set myDestinationWorksheet = .Worksheets.Add
    End With
 
    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
 
    'identify row and column numbers that define source data cell range
    myFirstRow = 1
    myLastRow = LastRow
    myFirstColumn = 1
    myLastColumn = LastColumn
 
    'obtain address of source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With
 
    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNewSheet")
    
End Sub

[TenDeadGoats]

Hi Guys,

I was able to get some help on Reddit. Turns out the issue was that my worksheet name had spaces in it which was causing problems. I needed to put a single quotation around the worksheet name to make it work. I change the last line of code out for this.

Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:="'" & myDestinationWorksheet.Name & "'!" & myDestinationRange, TableName:="PivotTableNewSheet")

[janamacon]

To create a pivot table, you need a heading value for each column. Tip: If you create an Excel Table from your data, column headings are automatically added to columns with blank heading cells, and you can avoid this error.