Unfortunately I ran into a problem with this that I can't seem to solve. I thought everything was running perfectly but its not. Basically what I'm trying to do is take the date from column D3 on the Visible sheet (not knowing how many different records there are),

1. make a PivotTable from it grouping the hours together,
2. removing the Grand Total line from the Pivot Table
3. copy the pivot table and paste the values of it on a new sheet
4. Then take the table and make a Graph out of it

Somehow since this is a macro, I have to pick a variable range. I'm not sure how to do that with this code

This is the code I am using as of now, but I am getting an error (I'm not sure how to set the variable range)

The ByHour sheet is the one that the pivot table is being copied to and the graph is on.

Sometimes I'm getting a 'Cannot Group Selection' Error for this part
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, True, _
False, False, False, False)

It is also adding a (blank) record into the Pivot table I have no idea why. It might be because I am selecting too many cells.

This is all based on a date range. Depending on the range I might get by that. Sometimes for making the graph it says "Subscript out of range"

I'm doing this at work, thought I had all the kinks out of it and my boss wants me to show it soon, if you can help out please do!


Thanks,

~J

Sub CallHours()

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Visible!R2C1:R22C11").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="created"
ActiveSheet.PivotTables("PivotTable1").PivotFields("created").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With
Range("A5").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, True, _
False, False, False, False)
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet2").Select
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Ticket Hour Interval"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Hour"
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("3:3").Select
Selection.Font.Bold = True
Range("D1").Select
ActiveCell.FormulaR1C1 = "From:"
Range("D2").Select
ActiveCell.FormulaR1C1 = "To:"
Range("D1:D2").Select
Selection.Font.Bold = True
Range("E1").Select
ActiveCell.FormulaR1C1 = "=TSC!R[1]C[-4]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=TSC!RC[-3]"
Range("A1").Select
Selection.Font.Bold = True
Range("A3").Select
Selection.CurrentRegion.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 33
Range("A3").Select
Selection.CurrentRegion.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("By Hour").Range("A3:B14"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="By Hour"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Tickets by Hour Interval"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour Interval"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# of Tickets"
End With
ActiveChart.HasLegend = False
ActiveWindow.Visible = False
Windows("TSCMainLookup.xls").Activate
Range("E1:E2").Select
Selection.Font.Bold = True
Sheets("Sheet3").Select
Range("A1").Select
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "By Hour"
Range("A1").Select
Sheets("Sheet2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Range("A1").Select
Sheets("By Hour").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft -39.75
ActiveSheet.Shapes("Chart 1").IncrementTop -60#
ActiveWindow.Visible = False
Windows("TSCMainLookup.xls").Activate
Range("F20").Select
ActiveCell.FormulaR1C1 = "Total Tickets = "
Range("F20").Select
Columns("F:F").EntireColumn.AutoFit
Range("G20").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
Range("F20:G20").Select
Selection.Font.Bold = True
Range("A1").Select
End Sub