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.
Reply With Quote
