PDA

View Full Version : [SOLVED] PIVOT TABLE TROUBLES - Code runs on one workbook but not the other



TenDeadGoats
05-07-2020, 11:12 AM
Hi Guys,

I am absolutely stumped on this one. I was stuck on a chunk of code yesterday until a gentleman from Reddit jumped in and helped me get it figured out. The chunk that he helped me work on was working perfectly, until I copied and pasted it into my larger body of code and now it isn't working again and giving me the same problem as before. I hope this is an easy fix, it seems like it should be given the fact that it was literally JUST WORKING. I apologize in advance for posting all the code but I'm not sure what is important and what isn't so I just put it all in.

here is the original code that was working perfectly.


Sub Table_Insert()

Dim LastColumn As Integer
Dim LastRow As Integer


Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
With tbl
.ListColumns.Add
.HeaderRowRange(.Range.Columns.Count) = "Client Name"
.HeaderRowRange(.Range.Columns.Count).Offset(1).Formula = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
.HeaderRowRange(.Range.Columns.Count).Columns.EntireColumn.AutoFit
End With

'Thanks PAULKED for the above part!!!



LastColumn = ActiveSheet.ListObjects("Table1").Range.Columns.Count
LastRow = ActiveSheet.ListObjects("Table1").Range.Rows.Count






'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



Once I copy that into my main code (down below) it gives me an error on the very last line of code.


Sub WholeShebang()



'OPEN


'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
'Show the dialog box
.Show

'Store in fullpath variable
Dim fullpath As String
fullpath = .SelectedItems.Item(1)

End With

'It's a good idea to still check if the file type selected is accurate.
'Quit the procedure if the user didn't select the type of file we need.
If InStr(fullpath, ".xls") = 0 Then
Exit Sub
End If

'Open the file selected by the user
Workbooks.Open fullpath


'SAVE


'Want to save a copy of data with same name first so we can do edits in the ActiveBook
Dim FileName As String


'Copy activesheet to the new workbook
'ActiveSheet.Copy

MsgBox "This new workbook will be saved as Consolidated Commissions Statements " & _
Format(Date, "ddmmmyyyy") & " - " & _
Format(Time, "hh mm AM/PM") & ".xlsx"

'Save new workbook as MyWb.xls(x) into the folder where ThisWorkbook is stored
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Consolidated Commissions Statements " & _
Format(Date, "ddmmmyyyy") & " - " & _
Format(Time, "hh mm AM/PM") & ".xlsx", xlWorkbookDefault


'FORMAT THE NEW WORKBOOK


' Rename the current sheet
ActiveSheet.Name = "Commissions Data"


' Create, name, and color the other needed sheets
Sheets.Add(After:=Sheets("Commissions Data")).Name = "Client Distribution"
Sheets.Add(After:=Sheets("Client Distribution")).Name = "Issuer Distribution"


Sheets("Commissions Data").Tab.ColorIndex = 3
Sheets("Client Distribution").Tab.ColorIndex = 4
Sheets("Issuer Distribution").Tab.ColorIndex = 5

Sheets("Commissions Data").Select


'CREATE CLIENT PIVOT TABLE




Dim LastColumn As Integer
Dim LastRow As Integer


Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
With tbl
.ListColumns.Add
.HeaderRowRange(.Range.Columns.Count) = "Client Name"
.HeaderRowRange(.Range.Columns.Count).Offset(1).Formula = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
.HeaderRowRange(.Range.Columns.Count).Columns.EntireColumn.AutoFit
End With

LastColumn = ActiveSheet.ListObjects("Table1").Range.Columns.Count
LastRow = ActiveSheet.ListObjects("Table1").Range.Rows.Count



'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 = ActiveWorkbook.Worksheets("Commissions Data")
Set myDestinationWorksheet = ActiveWorkbook.Worksheets("Client Distribution")

End With

MsgBox "Source Worksheet is " + mySourceWorksheet.Name
MsgBox "Destination Worksheet is " + myDestinationWorksheet.Name

'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

MsgBox mySourceData

'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


The MsgBox error checkers I have show that the proper destination sheet and source sheet are being selected. I have no idea why this is working in one workbook and not in the other. Anyone who can solve this is welcome to come to my birthday next year.

TenDeadGoats
05-07-2020, 11:27 AM
I was able to find a fix. In the last line I changed "ThisWorkbook.PivotCaches.Create(..." to "ActiveWorkbook.PivotCaches.Create(...".

Is this a robust enough fix that will work in the future or do I need to figure out a better solution?

paulked
05-07-2020, 01:31 PM
Activeworkbook is correct because you are still working with the workbook you opened and saved. Thisworkbook is the workbook you are running the code from :thumb