pivot table

[Guest]

hi...

i am creating a pivot table as many times there are sheets
in the workbook. each pivot needs to be created in a new
sheet. i am able to a new sheet. but my pivot is not
geting created ..nor it gives any error.

here is a part of a code:
For i = 1 To totSheets
Set NewSheet = Worksheets.Add
(before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "Summary " & arrWkShNames(f)
tmpStr = "Summary " & arrWkShNames(f)
Sheets("Summary " & arrWkShNames(f)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
arrWkShNames(f) & "!R" & rng1.Row & "C1:R" &
arrLRow(i) & "C" & rngUnderCost
(i).Column).CreatePivotTable TableDestination:="[" &
fileSaveName & "]" & tmpStr & "!R3C1", TableName _
:="PivotTable2",
DefaultVersion:=xlPivotTableVersion10
thanks a lot

 

[Tom Ogilvy]

It seems odd that you are using "f" as the index into your sheet names and
using "i" as the index into an array of row numbers and an offset to a range
reference to get a column number. I suspect that "f" and "i" may not be
coordinated and may be the source of your problem. Also, does rngUnderCost
ever change? Even if it does, rngUnderCost(i).Column should always return
the same column number regardless of i. This doesn't seem inconsistent, but
it seems odd to use a variable for the index if the results are constant.

 

[Guest]

yah all these values are well taken care of ... the (f)'s
and i's are not the problem.
the problem comes here:
TableDestination:="[" & fileSaveName & "]" & tmpStr & "!
R3C1"

if i give Tabledestination:="" then all my pivot are
created.

thanks for any feedback

-----Original Message-----
It seems odd that you are using "f" as the index into your sheet names and
using "i" as the index into an array of row numbers and an offset to a range
reference to get a column number. I suspect that "f" and "i" may not be
coordinated and may be the source of your problem. Also, does rngUnderCost
ever change? Even if it does, rngUnderCost(i).Column should always return
the same column number regardless of i. This doesn't seem inconsistent, but
it seems odd to use a variable for the index if the results are constant.

--
Regards,
Tom Ogilvy

hi...

i am creating a pivot table as many times there are sheets
in the workbook. each pivot needs to be created in a new
sheet. i am able to a new sheet. but my pivot is not
geting created ..nor it gives any error.

here is a part of a code:
For i = 1 To totSheets
Set NewSheet = Worksheets.Add
(before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "Summary " & arrWkShNames(f)
tmpStr = "Summary " & arrWkShNames(f)
Sheets("Summary " & arrWkShNames(f)).Select
ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase,
SourceData:= _
arrWkShNames(f) & "!R" & rng1.Row & "C1:R" &
arrLRow(i) & "C" & rngUnderCost
(i).Column).CreatePivotTable TableDestination:="[" &
fileSaveName & "]" & tmpStr & "!R3C1", TableName _
:="PivotTable2",
DefaultVersion:=xlPivotTableVersion10
thanks a lot

.