Automaically select the lastrow in PivotTable SourceData to avoid (blanks)
- by Adam
Hi
A little help needed, I have a Macro automatically creating pivot tables and charts, this is all working fine but I am getting (blank) in my pivot table becuase my range is all the way to 65536.
How do I automatically get the lastrow / column in my source data so I dont get any blanks. The data is changing constantly so this needs to be automatic
Here is the source data, I am looking to get the R65536C37 to be automatically generated based on the lastcolumn of the "raw" sheet
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"raw!R1C1:R65536C37").CreatePivotTable _
TableDestination:="Frontpage!R7C1", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
I have tried;
LastRow = ActiveSheet.UsedRange.Rows.Count
SourceData:= "raw!R1C1:" & LastRow & C37"
Pivot Macro
Sheets("Frontpage").Select
Range("A7").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"raw!R1C1:R65536C37").CreatePivotTable _
TableDestination:="Frontpage!R7C1", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
Sheets("Frontpage").Select
Cells(7, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Frontpage!$A$7:$H$22")
ActiveChart.ChartType = xlColumnClustered
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Priority")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Case ID"), "Count of Case ID", xlCount
ActiveChart.Parent.Name = "IncidentsbyPriority"
ActiveChart.ChartTitle.Text = "Incidents by Priority"
Dim RngToCover As Range
Dim ChtOb As ChartObject
Set RngToCover = ActiveSheet.Range("D7:L16")
Set ChtOb = ActiveSheet.ChartObjects("IncidentsbyPriority")
ChtOb.Height = RngToCover.Height ' resize
ChtOb.Width = RngToCover.Width ' resize
ChtOb.Top = RngToCover.Top ' reposition
ChtOb.Left = RngToCover.Left ' reposition
Any help would be greatly appreciated. I need to repeat this in four other pivots so as to avoid getting (blank) in my tables and charts.
