Need to turn this in to VBA

  • Thread starter jln via OfficeKB.com
  • Start date
J

jln via OfficeKB.com

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:=xlDatabase, 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").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField 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
 
G

galimi

You can get an row count on your sheet by using
activesheet.usedrange.rows.count
--
http://HelpExcel.com
(e-mail address removed)
516-984-0252


jln via OfficeKB.com said:
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:=xlDatabase, 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").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField 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
Click to expand...
 
J

jln via OfficeKB.com

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:=xlDatabase, 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").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField 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
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
Click to expand...
[quoted text clipped - 86 lines]
Range("G3").Select
End Sub
Click to expand...
Click to expand...
 
J

jln via OfficeKB.com

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?
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:=xlDatabase, 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").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField 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
Click to expand...
[quoted text clipped - 3 lines]
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

Help with Sum formula 2
Error 2007 0
VBA Pivot Table Error 1
Cleaner coding to repeat process 7
Creating Pivot Table with Macro using Multiple Sheets 0
Totals An Easier Way?? 5
Sum Columns with Specific Headings 6
Create Detail Sheets from Pivot Table 1

Top