TenDeadGoats
05-05-2020, 01:17 PM
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.Nam e & "!" & myDestinationRange, TableName:="PivotTableNewSheet")
End Sub
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.Nam e & "!" & myDestinationRange, TableName:="PivotTableNewSheet")
End Sub