I am creating an Add-in that will speed up creating Excel reports in Nprinting. The first function I am creating it creating a PivotTable. This is the Code I have created so far:
Sub AddPivot()
Dim ReportName As String
Dim ReportSheet As Worksheet, DataSheet As Worksheet
Dim WorkingBook As Workbook
Set WorkingBook = ActiveWorkbook
Set DataSheet = ActiveSheet
frmReportName.Show
ReportName = Replace(frmReportName.tbReportName.Value, " ", "")
DataSheet.ListObjects.Add(xlSrcRange, DataSheet.Range(DataSheet.Cells(1, 1), DataSheet.Cells(2, fnFinalColumn(DataSheet))), , xlYes).Name = ReportName
DataSheet.Range("A" & fnFinalRow(DataSheet) + 1).Value = "<deleterow>"
Set ReportSheet = WorkingBook.Sheets.Add
WorkingBook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ReportName, Version:=xlPivotTableVersion10).CreatePivotTable TableDestination:=ReportSheet.Range("A1"), _
TableName:="Pivot" & ReportName, DefaultVersion:=xlPivotTableVersion10
ReportSheet.Select
ReportSheet.Name = ReportName
With ReportSheet.PivotTables("Pivot" & ReportSheet).PivotCache
.RefreshOnFileOpen = True
.MissingItemsLimit = xlMissingItemsNone
End With
End Sub
Function fnFinalRow(ByVal wsToTest As Worksheet) As Long
With wsToTest
fnLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
End Function
Function fnFinalColumn(ByVal wsToTest As Worksheet) As Long
With wsToTest
fnLastColumn = .Cells(1, .Columns.Count).End(xlUp).Row
End With
End Function
Now, the code looks correct to me but I am receiving an error on this line:
DataSheet.ListObjects.Add(xlSrcRange, DataSheet.Range(DataSheet.Cells(1, 1), DataSheet.Cells(2, fnFinalColumn(DataSheet))), , xlYes).Name = ReportName
Here is the error I am receiving. I have stepped through the code and even set watches for Datasheet and checked that ReportName has a value (although ReportName being empty couldn't create that error).
What am I missing? is this something particular to Nprinting?
