Here is my code. THe problem im having is that I dont need it to sum every
column who do i make it only sum the colums i want?
Sub Forpayoffmismatch()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range

Set rng4 = Worksheets(1) _
..Cells(Rows.Count, 1).End(xlUp)(2)
Set rng5 = Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2)
rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1])"


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"qry_PoExport!R1C1:R71C26").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Investor_Number")

.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("Ancillary Fees"), "Count of Ancillary
Fees", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable3").Format xlTable7
Range("F3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Count of Ancillary
Fees"). _
Function = xlSum
Range("G3").Select
End Sub

--
Message posted via http://www.officekb.com

This line

Set rng5 = Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2)

set the range to some cells cell in column C, and then this line

rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1])"

create a SUM formula in that column, PLUD the next 3.

Which do you want to add it to?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jln via OfficeKB.com" <u25956@uwe wrote in message
news:683f42a324240@uwe...
Here is my code. THe problem im having is that I dont need it to sum every
column who do i make it only sum the colums i want?
Sub Forpayoffmismatch()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range

Set rng4 = Worksheets(1) _
Cells(Rows.Count, 1).End(xlUp)(2)
Set rng5 = Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2)
rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1])"


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"qry_PoExport!R1C1:R71C26").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Investor_Number")

.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("Ancillary Fees"), "Count of Ancillary
Fees", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable3").Format xlTable7
Range("F3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Count of Ancillary
Fees"). _
Function = xlSum
Range("G3").Select
End Sub

--
Message posted via http://www.officekb.com

THanks BOB I just found away around my problem.

Bob Phillips wrote:
This line

Set rng5 = Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2)

set the range to some cells cell in column C, and then this line

rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1])"

create a SUM formula in that column, PLUD the next 3.

Which do you want to add it to?

Here is my code. THe problem im having is that I dont need it to sum every
column who do i make it only sum the colums i want?
[quoted text clipped - 56 lines]
Range("G3").Select
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1