pivot..competely stuck up!

G

Guest

i am trying to create pivot tables for as many sheets
there are in the workbook. this pivot will use the data
from each worksheet but fields are the same.

I am unable to create the pivot. my loop runs fine i get
hte sheets created and renamed only the pivots are not
getting created...see code below...

any help would be appreaciated!
thanks...
totSheets = ActiveWorkbook.Worksheets.Count
xc = 1
For i = 1 To totSheets
Sheets.Add
ActiveSheet.Name = "Sum" & arrWkShNames(f)
nn = "Sum" & arrWkShNames(f)
mm = nn
If i = 1 Then
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
arrWkShNames(f) & "!R" & rng1.Row & "C1:R" &
arrLRow(i) & "C" & rngUnderCost
(i).Column).CreatePivotTable TableDestination:= _
"[" & fileSaveName & "]" & nn & "!R3C1",
TableName:="PivotTable" & i, _
DefaultVersion:=xlPivotTableVersion10
End If
If Not i = 1 Then
ActiveWorkbook.Worksheets(mm).PivotTables
("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:= _
"'[" & fileSaveName & "]" & nn & "'!R3C1",
TableName:="PivotTable" & i _
, DefaultVersion:=xlPivotTableVersion10
End If

ActiveSheet.PivotTables("PivotTable" & i).ColumnGrand
= False
ActiveSheet.PivotTables("PivotTable" & i).AddFields
RowFields:=Array("M-GROUP", _
"Data"), ColumnFields:="GF"
With ActiveSheet.PivotTables("PivotTable" &
i).PivotFields("Underutilisation")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable" & i).PivotFields
("Normalised Q4"). _
Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.Zoom = 90
f = f - 1
xc = xc + 1
Next i
 
Top