pivot table
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 (befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "Summary " & arrWkShNames(f) tmpStr = "Summary " & arrWkShNames(f) Sheets("Summary " & arrWkShNames(f)).Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 |
pivot table
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 wrote in message ... 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 (befo=ActiveWorkbook.Worksheets.Item(1)) NewSheet.Name = "Summary " & arrWkShNames(f) tmpStr = "Summary " & arrWkShNames(f) Sheets("Summary " & arrWkShNames(f)).Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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 |
pivot table
yah all these values are well taken care of ... the (f)'s
and i's are not the problem. the problem comes he 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 wrote in message ... 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 (befo=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 . |
All times are GMT +1. The time now is 06:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com