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 wha
I'm trying to do is take the date from column D3 on the Visible shee
(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 no
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'
not sure how to set the variable range)

The ByHour sheet is the one that the pivot table is being copied to an
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 ide
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 ge
by that. Sometimes for making the graph it says *"Subscript out o
range"*

I'm doing this at work, thought I had all the kinks out of it and m
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").AddField
RowFields:="created"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("created").Orientatio
= _
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("B
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 = "Hou
Interval"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# o
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

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

I tried putting this, but it came back with a Reference is not vali
error

Thanks for the help!

~J

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:
_
"MyData").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion1

--
nbaj2
-----------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648
View this thread: http://www.excelforum.com/showthread.php?threadid=56325

J,

Don't put quotes around the MyData (it is a range object, not a string):

Change:

SourceData:= _
"MyData")

to

SourceData:= _
MyData)

HTH,
Bernie
MS Excel MVP


"nbaj2k" wrote in message
...

I tried putting this, but it came back with a Reference is not valid
error

Thanks for the help!

~J

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

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


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

That was a stupid error on my part, I just thought that it had to be i
quotes for some reason

I changed it and now I get a different error.

I listed what I have now below. I'm having problems with the sam
section just getting a different error.

Now it is saying "Type mismath" and highliting this part.

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


Below is the whole section, in case that makes a difference.

I really appreciate your help.

~J

=======================

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:
_
MyData).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddField
RowFields:="created"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("created").Orientatio
= _
xlDataField
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False
False, True, _
False, False, False, False

--
nbaj2
-----------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648
View this thread: http://www.excelforum.com/showthread.php?threadid=56325

Earlier versions of Excel required a string (not a range object) for the SourceData parameter, so
try this:

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
MyData.Address(, , xlA1, True)).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
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, True, _
False, False, False, False)


HTH,
Bernie
MS Excel MVP


"nbaj2k" wrote in message
...

That was a stupid error on my part, I just thought that it had to be in
quotes for some reason

I changed it and now I get a different error.

I listed what I have now below. I'm having problems with the same
section just getting a different error.

Now it is saying "Type mismath" and highliting this part.

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


Below is the whole section, in case that makes a difference.

I really appreciate your help.

~J

=======================

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
MyData).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
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, True, _
False, False, False, False)


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

Alright it still can't get by that one spot. I'm using Excel 2003 so
would think I wouldn't need to use code from an earlier version, I jus
hope to find something that works.

Below is what I have right now.

I get an error back saying

"The PivotTable field name is not valid"

I don't have anything too fancy. I have columns starting in C2 wit
data in them, there is nothing in the first row except some labels an
a macro button.

I really have no clue now.

Any other ideas maybe?

Again I really appreciate it.

~J

Sub TestCallByHour2()

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
MyData.Address(, , xlA1, True)).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").Orientatio
= _
xlDataField
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False
True, _
False, False, False, False)
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With
End Su

--
nbaj2
-----------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648
View this thread: http://www.excelforum.com/showthread.php?threadid=56325

J,

Contact me privately (deitbe at consumer dot org) and I will send you a working version.

HTH,
Bernie
MS Excel MVP


"nbaj2k" wrote in message
...

Alright it still can't get by that one spot. I'm using Excel 2003 so I
would think I wouldn't need to use code from an earlier version, I just
hope to find something that works.

Below is what I have right now.

I get an error back saying

"The PivotTable field name is not valid"

I don't have anything too fancy. I have columns starting in C2 with
data in them, there is nothing in the first row except some labels and
a macro button.

I really have no clue now.

Any other ideas maybe?

Again I really appreciate it.

~J

Sub TestCallByHour2()

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
MyData.Address(, , xlA1, True)).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
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False,
True, _
False, False, False, False)
With ActiveSheet.PivotTables("PivotTable1")
ColumnGrand = False
RowGrand = False
End With
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