Just curious.

Why insert a sheet and then use that? Why not let the pivottable routine just
create that sheet for you?

wrote:

i resolved this error..

it was a very silly mistake.
the last column was coming one extra which caused the
error.
-----Original Message-----
hi debra

thanks for taking interest in replying to my query

i did what u said...see below. But still the pivot table
is not getting created. i get the new sheet created but
the pivot table is not getting created in that new sheet
test.
pls do suggest...
thanks again
Set NewSheet = Worksheets.Add
(Befo=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"
ActiveWorkbook.PivotCaches.Add
(SourceType:=xlDatabase,
SourceData:= _
rawDataWorksheetName & "!R1C1:R" & LastCellNum
& "C" & CU).CreatePivotTable
TableDestination:="'[Book1.xls]test'!R3C1", TableName:= _
"PivotTable5",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True

-----Original Message-----
It looks like you create the sheet, but don't use that
sheet as the
TableDestination. Try changing the line to include it:

'============================
Dim wb As Workbook
Set wb = ActiveWorkbook
Set NewSheet = Worksheets.Add _
(Befo=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDat abase, _
SourceData:=rawDataWorksheetName & "!R1C1:R" & _
LastCellNum & "C" & CU).CreatePivotTable _
TableDestination:="'[" & wb.Name & "]test'!R3C1", _
TableName:="PivotTable5"
'=========================

monika wrote:
Hi..

I am completely zapped at whats happening...i was
running
running the macro very nicely and all of a sudden the
its
giving error and i am unable to resolve it. I was
creating
a pivot table in a new sheet. and now the new sheet is
not
getting created somehow (#$%^&) and its processing on
the
same sheet from whihc it has to take pivot values and
gives a error "do u want to replace of destination
cells
in RawData?"

here is the piece of code where i create a pivot table
in
a new sheet. the new sheet gets created but somehow
the
pivot is not being created ...!
Set NewSheet = Worksheets.Add
(Befo=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"
ActiveWorkbook.PivotCaches.Add
(SourceType:=xlDatabase,
SourceData:= _
rawDataWorksheetName & "!R1C1:R" & LastCellNum
& "C" & CU).CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable5",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True

With ActiveSheet.PivotTables
("PivotTable5").PivotFields
("Test_Loc")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables
("PivotTable5").PivotFields
("Package")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables
("PivotTable5").PivotFields
("PL")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables
("PivotTable5").AddDataField
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields(trapCumVal), "Sum
of "
& trapCumVal, xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B6").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields
("Package").Subtotals = Array _
(False, False, False, False, False, False,
False,
False, False, False, False, False)

Range("A81").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields
("Test_Loc").Subtotals = _
Array(False, False, False, False, False,
False,
False, False, False, False, False, False)

ActiveSheet.Select
sheetname = "MSP"
please do suggest
thanks



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

.


--

Dave Peterson