VBA for Excel: Selection.Group Error

[goopit]

Hello,

I wrote the following macro to group and sum invoices by month. When I use the keyboard shortcut on results for a different company or a different date range, the message reads, "Run-time error '1004': Cannot group that selection".

This is the code.

Sub COOP()
'
' COOP Macro
' Macro recorded 11/10/2005 by .
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A2").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Detail Statistics - Invoiced Sa'!R1C1:R92C8").CreatePivotTable _
TableDestination:="", TableName:="PivotTable4"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Invoice Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Net Amount (Sum)")
.Orientation = xlDataField
.Position = 1
End With
Application.CommandBars("PivotTable").Visible = False
Range("A5").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
Columns("B:B").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Style = "Currency"
Range("B2").Select
End Sub

This is the highlighted section when I click for it to debug:

Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)

Any suggested fixes will be greatly appreciated.

goopit

 

[SkipVought]

goopit,

Is there a reason why you're doing this in VBA and not just using the Pivot Table Wizard?

Once a Pivot Table is setup and formatted, all you have to do is refresh when source data changes.

It's not like you have to ADD a pivot table every time you want to get a new view.

You can even make the Source Data range DYNAMIC.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]