+ Reply to Thread
Results 1 to 4 of 4

Help condensing macro that calls 8 macros.

Hybrid View

  1. 03-08-2017, 11:48 AM #1
    taylorsm
    taylorsm is offline
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Help condensing macro that calls 8 macros.

    I have a large macro that calls about 8 macros. I have it that way because I had to create it one step at a time and I am not good enough to do it in one go. It is ugly, but it works. My only issue really is that now my PERSONAL.XLSB has 8 macros listed in it that really belong to one. So by doing it this way my PERSONAL.XLSB has doubled its list length and makes it harder to find the correct macro.

    Most of the macros are just creating separate pivot tables, but due to the number of items that are excluded, the macro is long. Due to the length, I will have to do more than one post with the code i guess. I will also attached the macro.

    Basically, all I am looking for is to make this one large macro, or at least appear under PERSONAL.XLSB as one, but I doubt simply removing the SUB/END SUB would accomplish that lol


    Option Explicit
'Creates Pivots For ED Monthly Report
Sub MonthlyReportForED()

Call DCMRCombine
Call DCMRCorrectLoc
Call MonthlyPivot
Call PivotBorrowerAttempts
Call PivotBorrowerContactOut
Call PivotBorrowerContactIn
Call PivotBorrowerContactOutLocation
Call PivotBorrowerContactInLocation
Call PivotBorRehab
Call PivotBorRehabLoc
Call RemoveDups
Call DupsPivot
Call DupsPivotLoc
Call Headers

End Sub
Sub DCMRCombine()
Dim J As Long, k As Long, r As Range
J = Worksheets.Count
With Worksheets("DCMR")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
r.EntireRow.Delete
End With

For k = 1 To J
If Worksheets(k).Name = "DCMR" Then GoTo errorhandler
With Worksheets(k)
If .Range("A2") = "" Then GoTo errorhandler
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
r.EntireRow.Copy
Worksheets("DCMR").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("D:E").NumberFormat = "MM/DD/YY"
End With
errorhandler:
Next k
' Center Macro
'

'
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub
Sub DCMRCorrectLoc()
Dim LastRow As Long


LastRow = Range("J" & Rows.Count).End(xlUp).Row

  
    ActiveSheet.Range("A:N").AutoFilter Field:=9, Criteria1:=Array( _
        "Bca Newberry", "Dra Yakel", "Lee Bron", "Lee Copland", _
        "Sa Doughery", "Saa Douhtey"), Operator:=xlFilterValues
 Range("J2:J" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "CRI"
 
    ActiveSheet.Range("A:N").AutoFilter Field:=9, Criteria1:=Array( _
        "Sra Douerty", "Sra Dghtery"), Operator:=xlFilterValues
 

        
    ActiveSheet.Range("A:N").AutoFilter Field:=9, Criteria1:=Array( _
        "Cthia Cvez", "Lry Ber", "Rael no", "Thas Ras"), Operator:= _
        xlFilterValues
Range("J2:J" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "ARG"
ActiveSheet.Range("$A$1:$N$725").AutoFilter Field:=9

End Sub
Sub MonthlyPivot()
    Dim pc As PivotCache
    Dim ws As Worksheet

    
    With ActiveWorkbook
        For Each pc In .PivotCaches
            pc.MissingItemsLimit = xlMissingItemsNone
        Next pc
    End With
 Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Sheet1"
 End Sub
 'Pivot Borrower Attempts
 Sub PivotBorrowerAttempts()
 On Error Resume Next
     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R602C14", Version:=6).CreatePivotTable TableDestination:= _
        "sheet1!R2C1", TableName:="Pivot1", DefaultVersion:=6
    Sheets("sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Pivot1").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivot1").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Pivot1").AddDataField ActiveSheet.PivotTables( _
        "Pivot1").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("Pivot1").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("Pivot1").PivotFields("Call Type").CurrentPage = _
        "BORMESSAGE"
    With ActiveSheet.PivotTables("Pivot1").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivot1").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Pivot1").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("BVERIFY").Visible = False
        .PivotItems("CALLBCK").Visible = False
        .PivotItems("CALLESC").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("COLNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("HOLDTM").Visible = False
        .PivotItems("IDCRI").Visible = False
        .PivotItems("INACCDATA").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MENDISC").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBAWG").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCOLL").Visible = False
        .PivotItems("RHBCRDT").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDFR").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBIV").Visible = False
        .PivotItems("RHBLOAN").Visible = False
        .PivotItems("RHBONE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPAY").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREPAY").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSCH").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("RHBTOP").Visible = False
        .PivotItems("RHBTRAN").Visible = False
        .PivotItems("RHBTX").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
        .PivotItems("UNPROFESS").Visible = False
    End With
On Error GoTo 0
End Sub
    Attached Files Attached Files
    Last edited by taylorsm; 03-08-2017 at 11:51 AM.
    Reply With QuoteRegister To Reply
  2. 03-08-2017, 11:49 AM #2
    taylorsm
    taylorsm is offline
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Help condensing macro that calls 8 macros. 

    'Pivot Borrower Contact Out
Sub PivotBorrowerContactOut()
On Error Resume Next
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C5", TableName:="PivotTable2", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 5).Select
    With ActiveSheet.PivotTables("pivottable2").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("pivottable2").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("pivottable2").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("pivottable2").PivotFields("Call Type").CurrentPage = _
        "BORCONT"
    With ActiveSheet.PivotTables("pivottable2").PivotFields("Inbound or Out")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("pivottable2").AddDataField ActiveSheet.PivotTables( _
        "pivottable2").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("pivottable2").PivotFields("Inbound or Out"). _
        ClearAllFilters
    ActiveSheet.PivotTables("pivottable2").PivotFields("Inbound or Out"). _
        CurrentPage = "Out"
    With ActiveSheet.PivotTables("pivottable2").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("pivottable2").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MSG").Visible = False
        .PivotItems("MSGFOTI").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBAWG").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCOLL").Visible = False
        .PivotItems("RHBCRDT").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDFR").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBIV").Visible = False
        .PivotItems("RHBLOAN").Visible = False
        .PivotItems("RHBONE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPAY").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREPAY").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSCH").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("RHBTOP").Visible = False
        .PivotItems("RHBTRAN").Visible = False
        .PivotItems("RHBTX").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
    End With
On Error GoTo 0
End Sub
'Pivot Borrower Contact In
Sub PivotBorrowerContactIn()
On Error Resume Next
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C9", TableName:="PivotTable3", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 9).Select
      With ActiveSheet.PivotTables("pivottable3").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("pivottable3").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("pivottable3").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("pivottable3").PivotFields("Call Type").CurrentPage = _
        "BORCONT"
    With ActiveSheet.PivotTables("pivottable3").PivotFields("Inbound or Out")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("pivottable3").AddDataField ActiveSheet.PivotTables( _
        "pivottable3").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("pivottable3").PivotFields("Inbound or Out"). _
        ClearAllFilters
    ActiveSheet.PivotTables("pivottable3").PivotFields("Inbound or Out"). _
        CurrentPage = "In"
    With ActiveSheet.PivotTables("pivottable3").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("pivottable3").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MENDISC").Visible = False
        .PivotItems("MSG").Visible = False
        .PivotItems("MSGFOTI").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBAWG").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCOLL").Visible = False
        .PivotItems("RHBCRDT").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDFR").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBIV").Visible = False
        .PivotItems("RHBLOAN").Visible = False
        .PivotItems("RHBONE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPAY").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREPAY").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSCH").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("RHBTOP").Visible = False
        .PivotItems("RHBTRAN").Visible = False
        .PivotItems("RHBTX").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
    End With
On Error GoTo 0
End Sub
    Last edited by taylorsm; 03-08-2017 at 11:52 AM.
    Reply With QuoteRegister To Reply
  3. 03-08-2017, 11:50 AM #3
    taylorsm
    taylorsm is offline
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Help condensing macro that calls 8 macros. 

    'Pivot Borrower Contact Out
Sub PivotBorrowerContactOutLocation()
On Error Resume Next
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C13", TableName:="PivotTable4", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 13).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Call Type").CurrentPage = _
        "BORCONT"
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Inbound or Out")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Inbound or Out"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Inbound or Out"). _
        CurrentPage = "Out"
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MENDISC").Visible = False
        .PivotItems("MSG").Visible = False
        .PivotItems("MSGFOTI").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBAWG").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCOLL").Visible = False
        .PivotItems("RHBCRDT").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDFR").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBIV").Visible = False
        .PivotItems("RHBLOAN").Visible = False
        .PivotItems("RHBONE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPAY").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREPAY").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSCH").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("RHBTOP").Visible = False
        .PivotItems("RHBTRAN").Visible = False
        .PivotItems("RHBTX").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
    End With
On Error GoTo 0
End Sub
'Pivot Borrower Contact In
Sub PivotBorrowerContactInLocation()
On Error Resume Next
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C17", TableName:="PivotTable5", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 17).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Call Type").CurrentPage = _
        "BORCONT"
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Inbound or Out")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Inbound or Out"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Inbound or Out"). _
        CurrentPage = "In"
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MENDISC").Visible = False
        .PivotItems("MSG").Visible = False
        .PivotItems("MSGFOTI").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBAWG").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCOLL").Visible = False
        .PivotItems("RHBCRDT").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDFR").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBIV").Visible = False
        .PivotItems("RHBLOAN").Visible = False
        .PivotItems("RHBONE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPAY").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREPAY").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSCH").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("RHBTOP").Visible = False
        .PivotItems("RHBTRAN").Visible = False
        .PivotItems("RHBTX").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
    End With
On Error GoTo 0
End Sub
    Last edited by taylorsm; 03-08-2017 at 11:52 AM.
    Reply With QuoteRegister To Reply
  4. 03-08-2017, 11:52 AM #4
    taylorsm
    taylorsm is offline
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Help condensing macro that calls 8 macros. 

    'Pivot Borrower Contact In
Sub PivotBorRehab()
On Error Resume Next
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C21", TableName:="PivotTable6", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 21).Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable6").PivotFields("Call Type").CurrentPage = _
        "BORREHAB"
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Inbound or Out")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("PivotTable6").PivotFields("Inbound or Out"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable6").PivotFields("Inbound or Out"). _
        CurrentPage = "In"
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("BVERIFY").Visible = False
        .PivotItems("CALLBCK").Visible = False
        .PivotItems("CALLESC").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("COLNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("HOLDTM").Visible = False
        .PivotItems("IDCRI").Visible = False
        .PivotItems("INACCDATA").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MENDISC").Visible = False
        .PivotItems("MINIMIR").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
        .PivotItems("UNPROFESS").Visible = False
    End With
On Error GoTo 0
End Sub
'Pivot Borrower Contact In
Sub PivotBorRehabLoc()
On Error Resume Next
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DCMR!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C25", TableName:="PivotTable7", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 25).Select
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Call Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Call Type"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Call Type").CurrentPage = _
        "BORREHAB"
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Inbound or Out")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("Error Type"), "Count of Error Type", xlCount
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Inbound or Out"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Inbound or Out"). _
        CurrentPage = "In"
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Error Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Error Type")
        .PivotItems("3PTYDISC").Visible = False
        .PivotItems("3RDLOC").Visible = False
        .PivotItems("ADMRV").Visible = False
        .PivotItems("AWGDATE").Visible = False
        .PivotItems("AWGHRNG").Visible = False
        .PivotItems("AWGRHB").Visible = False
        .PivotItems("BIF").Visible = False
        .PivotItems("BVERIFY").Visible = False
        .PivotItems("CALLBCK").Visible = False
        .PivotItems("CALLESC").Visible = False
        .PivotItems("CLTNAME").Visible = False
        .PivotItems("COLNAME").Visible = False
        .PivotItems("CONCAL").Visible = False
        .PivotItems("CONELG").Visible = False
        .PivotItems("CONMNTH").Visible = False
        .PivotItems("CONNEW").Visible = False
        .PivotItems("CONPIF").Visible = False
        .PivotItems("CONREPAY").Visible = False
        .PivotItems("CONSOL").Visible = False
        .PivotItems("CURRBAL").Visible = False
        .PivotItems("DCPAY").Visible = False
        .PivotItems("Demo").Visible = False
        .PivotItems("EMAILD").Visible = False
        .PivotItems("EXDNC").Visible = False
        .PivotItems("EXTCODE").Visible = False
        .PivotItems("FOLUP").Visible = False
        .PivotItems("FRNTSCRN").Visible = False
        .PivotItems("HOLDTM").Visible = False
        .PivotItems("IDCRI").Visible = False
        .PivotItems("INACCDATA").Visible = False
        .PivotItems("INACCNOTE").Visible = False
        .PivotItems("LTTRS").Visible = False
        .PivotItems("MENDISC").Visible = False
        .PivotItems("MINIMIR").Visible = False
        .PivotItems("NOCC").Visible = False
        .PivotItems("NOERROR").Visible = False
        .PivotItems("PERMSSN").Visible = False
        .PivotItems("PYMNTPLN").Visible = False
        .PivotItems("RECDCALL").Visible = False
        .PivotItems("REPAY").Visible = False
        .PivotItems("RHBAPLY").Visible = False
        .PivotItems("RHBASK").Visible = False
        .PivotItems("RHBBIF").Visible = False
        .PivotItems("RHBCAL").Visible = False
        .PivotItems("RHBCHK").Visible = False
        .PivotItems("RHBCSTS").Visible = False
        .PivotItems("RHBDUE").Visible = False
        .PivotItems("RHBDWN").Visible = False
        .PivotItems("RHBFEE").Visible = False
        .PivotItems("RHBORDR").Visible = False
        .PivotItems("RHBPERK").Visible = False
        .PivotItems("RHBPLAN").Visible = False
        .PivotItems("RHBPLCS").Visible = False
        .PivotItems("RHBPPR").Visible = False
        .PivotItems("RHBPYMT").Visible = False
        .PivotItems("RHBRAL").Visible = False
        .PivotItems("RHBREF").Visible = False
        .PivotItems("RHBREQ").Visible = False
        .PivotItems("RHBSTND").Visible = False
        .PivotItems("SIF").Visible = False
        .PivotItems("STATE").Visible = False
        .PivotItems("UNPROFESS").Visible = False
    End With
On Error GoTo 0
End Sub
' Duplicates DCMR to Remove Duplicates
Sub RemoveDups()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RemoveDups"
    Sheets("DCMR").Select
    Cells.Select
    Selection.Copy
    Sheets("RemoveDups").Select
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub
' Pivot To Remove Duplicates
Sub DupsPivot()
     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RemoveDups!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C29", TableName:="PivotTable8", DefaultVersion:=6
    Sheets("Sheet1").Select
     Cells(2, 29).Select
         ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("Account"), "Count of Account", xlCount
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Call Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Inbound or Out")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable8").PivotFields("Account").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
End Sub
'Pivot To Remove Duplicates With Location
Sub DupsPivotLoc()
     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RemoveDups!R1C1:R3000C14", Version:=6).CreatePivotTable TableDestination:= _
        "'Sheet1'!R2C33", TableName:="PivotTable9", DefaultVersion:=6
     Sheets("Sheet1").Select
     Cells(2, 33).Select
         ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Account"), "Count of Account", xlCount
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Date Reviewed")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Inbound or Out")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Call Type")
        .Orientation = xlRowField
        .Position = 2
    End With
End Sub
Sub Headers()
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Phone Calls Attempted"
    Range("A1:B1").Select
    Selection.Merge
    
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Borrower Contacts"
    Range("E1:F1").Select
    Selection.Merge
    
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Borrower Call-Ins"
    Range("I1:J1").Select
    Selection.Merge
    
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Borrower Contacts With Location"
    Range("M1:N1").Select
    Selection.Merge
    
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Borrower Call-Ins With Location"
    Range("Q1:R1").Select
    Selection.Merge
    
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "Rehab's Offered"
    Range("U1:V1").Select
    Selection.Merge
    
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "Rehab's Offered With Location"
    Range("Y1:Z1").Select
    Selection.Merge
    
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Call Types In and Out"
    Range("AC1:AD1").Select
    Selection.Merge
   
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "Call Types With Location"
    Range("AG1:AH1").Select
    Selection.Merge

    Range("A1:B1,E1:F1,I1:J1,M1:N1,Q1:R1,U1:V1,Y1:Z1,AC1:AD1,AG1:AH1").Select
    With Selection.Interior
        .Color = 65535
    End With
    
    Rows("1:1").Select
    Selection.HorizontalAlignment = xlCenter
    
    Range("A1").Select
End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Creating a Macro that calls another Macro Dynamically
    By Remotruker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2015, 02:34 PM
  2. Select All Checkboxes Checkbox That Also Calls Checkbox Macros
    By Intervigilium in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2015, 06:41 PM
  3. Macro that calls other macros in different sheets (Mudraker)
    By mlcfexcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2007, 08:56 PM
  4. Macro that calls other macros in different sheets
    By mlcfexcel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-05-2007, 07:44 AM
  5. [SOLVED] My menu calls an old macro
    By Claus Højlund in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2006, 12:05 PM
  6. [SOLVED] Condensing a macro
    By EstherJ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2006, 10:35 AM
  7. [SOLVED] Problem in updating all worksheets of a workbook using a macro that calls another macro
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2006, 01:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1