.

Sami · Mar 17th, 2002, 10:48 AM

.

[ This Message was edited by: Sami on 2002-03-21 21:10 ]

LMF · Mar 17th, 2002, 11:11 AM

hi Sami, just wondering if you are doing A-level computing or ICT?

GeorgeB · Mar 17th, 2002, 06:38 PM

Try this
Public Sub ByRegion()
'NOTES IN THIS SUB ARE TYPICAL OF ALL SUBS IN THIS MODULE
'Erase existing worksheet or chart if present.
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("By Region").Delete
Charts("By Region Chart").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Select the input sheet and range A2
Worksheets("INPUT").Select
Range("A60").Select
'Create pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("A60").CurrentRegion.Address).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="NAME", _
ColumnFields:="M", PageFields:="REGION"
ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT").Orientation = _
xlDataField

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 17th, 2002, 10:48 AM	#1
Sami Board Regular Join Date: Mar 2002 Posts: 122	. [ This Message was edited by: Sami on 2002-03-21 21:10 ]

Mar 17th, 2002, 11:11 AM	#2
LMF Board Regular Join Date: Mar 2002 Posts: 73	hi Sami, just wondering if you are doing A-level computing or ICT?

Mar 17th, 2002, 06:38 PM	#3
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	Try this Public Sub ByRegion() 'NOTES IN THIS SUB ARE TYPICAL OF ALL SUBS IN THIS MODULE 'Erase existing worksheet or chart if present. Application.ScreenUpdating = False On Error Resume Next Application.DisplayAlerts = False Sheets("By Region").Delete Charts("By Region Chart").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Select the input sheet and range A2 Worksheets("INPUT").Select Range("A60").Select 'Create pivot table ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Range("A60").CurrentRegion.Address).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="NAME", _ ColumnFields:="M", PageFields:="REGION" ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT").Orientation = _ xlDataField