ExcelBanter - Need to turn this in to VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Need to turn this in to VBA (https://www.excelbanter.com/excel-programming/375831-need-turn-vba.html)

Need to turn this in to VBA

Here is a recorded macro that i need to turn into VBA. WHat i need to change
is I need it to count the rows then place the totals at the bottom. On the
pivottable i need it to count the number of different investor numbers.

Sub PivotTable2()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

'
Range("A1").Select
Selection.End(xlDown).Select
Range("B72").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-70]C:R[-1]C)"
Range("C72").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-70]C:R[-1]C)"
Range("D72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("E72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("F72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("H72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("I72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("K72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("L72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("M72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("N72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("O72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("P72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Q72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("R72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Y72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Z72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
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 OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Need to turn this in to VBA

Here is an updated work in progress. I have it doing the total now at the
right place but it not totaling all the columns that i need can some one see
what im doim wrong. It only totals columns C -F I think the problem is in my
resize.

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

galimi wrote:
You can get an row count on your sheet by using
activesheet.usedrange.rows.count
Here is a recorded macro that i need to turn into VBA. WHat i need to change
is I need it to count the rows then place the totals at the bottom. On the
[quoted text clipped - 86 lines]
Range("G3").Select
End Sub

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

Need to turn this in to VBA

OK i got that working but the resize totals ever column and there only
certain ones that i need. Is there something I can use other then resize?

jln wrote:
Here is an updated work in progress. I have it doing the total now at the
right place but it not totaling all the columns that i need can some one see
what im doim wrong. It only totals columns C -F I think the problem is in my
resize.

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

You can get an row count on your sheet by using
activesheet.usedrange.rows.count
[quoted text clipped - 3 lines]
Range("G3").Select
End Sub

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