I have the following information in an Excel Spreadsheet Office 2000.

The data consists of 11 columns and is updated once a week and the rows can
increase or decrease depending on what Order Date is selected. The
information required is Acc., Name, Part No on the left of the pivot table
(ROW) and the Qty in the middle (DATA). The Qty shown should give the Sum of
Qty and not the Count of Qty. Each time when the macro is run, the Pivot
Table gives me the Count of Qty which is a bit frustrating. What I would
like is that when the macro is selected it picks all the data but NOT the
last row of the first column which just shows a square character, (The square
character is always put in the last row of the first column every time the
data is imported.) and then the Pivot table generates the Acc, Name, Part
No,in the (ROW) and Sum of Qty in the (DATA)

Acc Name Part No Des Qty Col E to Col K ,
42606 ZOL 4140 Assy 25
42004 SAT 4790 Button 150
41930 SAI 5120 Handle 300
42004 SAT 5120 Handle 600
42004 SAT 5155 Exterior 100
41911 TOM 5645 Locker 100
41930 SAI 7140 Lock 200
42004 SAT 7140 Lock 150
41917 CAT 7150 Interior 60
41917 CAT 7155 Interior 40
42004 SAT 7273 Clamp 2000
I have recorded the macro as follows:

Sub PivotTable()

Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Range(Selection, Cells(1)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"AccNoSaleHi1!R1C1:R9999C11").CreatePivotTable TableDestination:="",
TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Acc
No", "Acc Name", "Part No")
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Qty").Orientation =
xlDataField

End Sub

Would appreciate if someone could help me.
Thanks

You could base the pivot table on a dynamic range, then just refresh it
when the data changes, instead of rebuilding. There are instructions he

http://www.contextures.com/xlPivot01.html

Change the formula so it subtracts 1 from the count in column A, e.g.:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A)-1,7)

to eliminate the row that contains only the square character.


Johnny wrote:
I have the following information in an Excel Spreadsheet Office 2000.

The data consists of 11 columns and is updated once a week and the rows can
increase or decrease depending on what Order Date is selected. The
information required is Acc., Name, Part No on the left of the pivot table
(ROW) and the Qty in the middle (DATA). The Qty shown should give the Sum of
Qty and not the Count of Qty. Each time when the macro is run, the Pivot
Table gives me the Count of Qty which is a bit frustrating. What I would
like is that when the macro is selected it picks all the data but NOT the
last row of the first column which just shows a square character, (The square
character is always put in the last row of the first column every time the
data is imported.) and then the Pivot table generates the Acc, Name, Part
No,in the (ROW) and Sum of Qty in the (DATA)

Acc Name Part No Des Qty Col E to Col K ,
42606 ZOL 4140 Assy 25
42004 SAT 4790 Button 150
41930 SAI 5120 Handle 300
42004 SAT 5120 Handle 600
42004 SAT 5155 Exterior 100
41911 TOM 5645 Locker 100
41930 SAI 7140 Lock 200
42004 SAT 7140 Lock 150
41917 CAT 7150 Interior 60
41917 CAT 7155 Interior 40
42004 SAT 7273 Clamp 2000
I have recorded the macro as follows:

Sub PivotTable()

Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Range(Selection, Cells(1)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"AccNoSaleHi1!R1C1:R9999C11").CreatePivotTable TableDestination:="",
TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Acc
No", "Acc Name", "Part No")
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Qty").Orientation =
xlDataField

End Sub

Would appreciate if someone could help me.
Thanks



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html