Macro Help!!!

A

Al Mackay

Could anyone help on the following problem @ all?

I have a macro that automatically generates a pivot table and then a
pivot chart based on data imported into a spreadsheet.

I've enclosed the whole macro - however this only fails when the macro
tries to group the data by Month / Year (i've highlighted the section
towards the end of the macro where this fails and debug appears).

Could anyone advise if there is anything obvious as to why this
doesn't work?

My data structure is made up like:
Column A: ID Reference (Unique each line)
Column B: Category (may be several records with the same category)
Column C: Team (may be several records with the same team)
Column D: Country (may be several records with the same country)
Column E: Raised Date (format DD/MM/YY - may be several records with
the same raised date)
Column F: Completed Date (format DD/MM/YY - may be several records
with the same completed date)
Column G: Status (may be several records with the same status)

Really appreciate your helo on this and apologise the long message!

Many Thanks, Al ( [email protected] )

Sub Pivot_Charts()
Sheets("WR (1,2,3,5)").Select
Range("A:A,B:B,C:C,H:H,K:K,N:O").Select

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot_Data"
Range("A1").Select
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Pivot_Table"
Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Pivot_Data").Select
Columns("E:F").Select
Selection.NumberFormat = "dd/mm/yy"
Range("A1").Select
Sheets("Pivot_Table").Select
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
"=OFFSET(Pivot_Data!R4C1,0,0,COUNTA(Pivot_Data!C1),7)"
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _
"=OFFSET(Pivot_Data!R4C1,0,0,COUNTA(Pivot_Data!C1),7)"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="Data"). _
CreatePivotTable TableDestination:=Range("A1"),
TableName:="PivotTable3"
ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
ActiveSheet.PivotTables("PivotTable3").PivotCache.RefreshOnFileOpen
= True
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:= _
"Incident Raised Date", ColumnFields:="Status",
PageFields:=Array( _
"Originating Country", "Team", "Work Category")
ActiveSheet.PivotTables("PivotTable3").PivotFields("Incident
Raised Date"). _
Orientation = xlDataField
Range("A13").Select

*********************THIS SECTION DOESN'T
WORK*******************************
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, False, _
False, True, False, True)
******************************END OF
SECTION*********************************
Charts.Add

ActiveChart.ChartType = xlLineMarkers
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"Chart showing Work Requests by Priority / Team / Country"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Month / Year"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Number"
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Application.CommandBars("PivotTable").Visible = False
ActiveChart.Deselect
Application.DisplayFullScreen = False
Sheets("Pivot_Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Pivot_Table").Select
ActiveWindow.SelectedSheets.Visible = False
Application.DisplayFullScreen = True
ActiveWorkbook.SaveAs
Filename:="C:\Work_Requests_Charts_2003.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Range("B5").Select
End Sub
 
Top