urgent-pivot table

M

monika

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
(Before:=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
 
D

Debra Dalgleish

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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

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

Guest

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
(Before:=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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=rawDataWorksheetName & "!R1C1:R" & _
LastCellNum & "C" & CU).CreatePivotTable _
TableDestination:="'[" & wb.Name & "]test'!R3C1", _
TableName:="PivotTable5"
'=========================
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
(Before:=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
Click to expand...


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

.
Click to expand...
 
G

Guest

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
(Before:=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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=rawDataWorksheetName & "!R1C1:R" & _
LastCellNum & "C" & CU).CreatePivotTable _
TableDestination:="'[" & wb.Name & "]test'!R3C1", _
TableName:="PivotTable5"
'=========================
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
(Before:=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
Click to expand...


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

.
Click to expand...
.
Click to expand...
 
D

Dave Peterson

Just curious.

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

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
(Before:=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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
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
(Before:=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

.
Click to expand...
.
Click to expand...
Click to expand...
 
D

Dave Peterson

One way to avoid this kind of stuff:
TableDestination:="'[" & wb.Name & "]test'!R3C1", _

is to use:
TableDestination:=newsheet.cells(3,1).address(external:=true), _
or
TableDestination:=newsheet.range("A3").address(external:=true), _

But I wouldn't insert a newsheet first anyway--just let the pivottable do it.

And since the sheet with the new pivottable is active, I could use:

set newSheet = activesheet

after the lines that created the new worksheet.



Debra said:
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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=rawDataWorksheetName & "!R1C1:R" & _
LastCellNum & "C" & CU).CreatePivotTable _
TableDestination:="'[" & wb.Name & "]test'!R3C1", _
TableName:="PivotTable5"
'=========================
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
(Before:=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
Click to expand...
Click to expand...
 
D

Debra Dalgleish

You're right -- I don't know what I was thinking. In my own code, I'd
just let Excel create the sheet, then I'd give it a name.
(My only excuse is that it was really late <g>)

Dave said:
One way to avoid this kind of stuff:
TableDestination:="'[" & wb.Name & "]test'!R3C1", _

is to use:
TableDestination:=newsheet.cells(3,1).address(external:=true), _
or
TableDestination:=newsheet.range("A3").address(external:=true), _

But I wouldn't insert a newsheet first anyway--just let the pivottable do it.

And since the sheet with the new pivottable is active, I could use:

set newSheet = activesheet

after the lines that created the new worksheet.



Debra said:
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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=rawDataWorksheetName & "!R1C1:R" & _
LastCellNum & "C" & CU).CreatePivotTable _
TableDestination:="'[" & wb.Name & "]test'!R3C1", _
TableName:="PivotTable5"
'=========================
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
(Before:=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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I like to use:

It was too {early|close to lunch|close to quitting|late}.

Always leave your options open <vbg>.



Debra said:
You're right -- I don't know what I was thinking. In my own code, I'd
just let Excel create the sheet, then I'd give it a name.
(My only excuse is that it was really late <g>)

Dave said:
One way to avoid this kind of stuff:
TableDestination:="'[" & wb.Name & "]test'!R3C1", _

is to use:
TableDestination:=newsheet.cells(3,1).address(external:=true), _
or
TableDestination:=newsheet.range("A3").address(external:=true), _

But I wouldn't insert a newsheet first anyway--just let the pivottable do it.

And since the sheet with the new pivottable is active, I could use:

set newSheet = activesheet

after the lines that created the new worksheet.



Debra said:
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 _
(Before:=ActiveWorkbook.Worksheets.Item(1))
NewSheet.Name = "test"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
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
(Before:=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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
Pivot table - unwanted lines 2
Pivot Table from Filtered Data 2
Playing back a Pivot Table macros 2
Pivot table code question 2
Format Pivot total rows 2
VBA SQL Server Stored Procedure creating a Pivot Table 7
syntax in VBA to create a pivot table 2

Top