Dear All

I have the following VBA script which cleans up columns of data that I
obtain using a series of Web Queries, and then creates a Pivot Table
for that worksheet. It works fine except for two (2) problems:

1. it grabs as much of the worksheet as possible (hence the
"R1C1:R65000C11") instead of finding only the cells containing data,
which is what a Pivot Table automatically does when you create one; and
2. I have to do each worksheet at a time instead of it automatically
moving to the next worksheet when I have many worksheets.

How can I modify it so that it only grabs cells with data and moves to
the next worksheet when it has completed the active worksheet?

(Please ignore the strange characters in the script because these are
Japanese characters and cannot show up in here...)

Thanks!

Regards
Pelham


Sub Pivot()
'
' Pivot Macro
' Macro recorded 14/11/2006
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Range("A2").Select
ActiveWindow.FreezePanes = True
Rows("1:1").Select
Selection.Font.Bold = True
Columns("A:A").EntireColumn.AutoFit
Selection.AutoFilter
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.NumberFormat = "#,##0"
Selection.copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("H:H").Select
Selection.NumberFormat = "0.00"
Rows("1:1").Select
Selection.Font.Bold = True
Range("C7").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"R1C1:R65000C11").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Ward", _
"Data"), ColumnFields:="Type", PageFields:="City"
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Rent(€°~)")
.Orientation = xlDataField
.Caption = "Average of Rent(€°~)"
.Position = 1
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Area")
.Orientation = xlDataField
.Caption = "Average of Area"
.Position = 2
.Function = xlAverage
End With
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("€°~Â�^€¢Â½€¢Ã„")
.Orientation = xlDataField
.Caption = "Average of €°~Â�^€¢Â½€¢Ã„"
.Function = xlAverage
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
Cells.Select
Selection.NumberFormat = "#,##0"
With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
Range("D12").Select
Columns("A:A").ColumnWidth = 8.86
End Sub