Can someone help me with this error?

[nbaj2k]

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

[Bernie Deitrick]

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:=xlDatabase, SourceData:= _
myPTSource).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

USe the code above to replace this:

> 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)


HTH,
Bernie
MS Excel MVP


"nbaj2k" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> nbaj2k
> ------------------------------------------------------------------------
> nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
> View this thread: http://www.excelforum.com/showthread...hreadid=563251
>

[nbaj2k]

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:=xlDatabase, SourceData:= _
"MyData").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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:=xlDatabase, 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
>

[nbaj2k]

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 highlighting this part.

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


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

Its probably another small error.

I really appreciate your help.

~J

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

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

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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)

[Bernie Deitrick]

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:=xlDatabase, 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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:=xlDatabase, 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:=xlDatabase, 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
>

[nbaj2k]

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:=xlDatabase, 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

[Bernie Deitrick]

J,

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

HTH,
Bernie
MS Excel MVP


"nbaj2k" <[email protected]> wrote in message
news:[email protected]...
>
> 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:=xlDatabase, 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
>