This short code segment will allow you to use a variable range with your pivot table creation.

Dim myPTSource As Range
Set myPTSource = Range("D3").CurrentRegion

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
myPTSource).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

USe the code above to replace this:

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Visible!R2C1:R22C11").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)


HTH,
Bernie
MS Excel MVP


"nbaj2k" wrote in message
...

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:=xlDatab ase, 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 Whe=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


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=563251