ikon  

BANKACILAR İÇİN MAKROLAR - Deniz Levent Ziştoğlu

1- Risk Merkezi'nden Çekilen Memzuç Bilgilerinin Düzenlenmesi

 
 
Sub memzuc()

'

' memzuc Macro

'

 

'

   Range("E2:J2").Select

    Range(Selection, Selection.End(xlDown)).Select

   For Each xCell In Selection

   xCell.Value = CDec(xCell.Value)

   Next xCell

    Range("D12").Select

    Application.CutCopyMode = False

    Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "Sheet0!R1C1:R5720C11", Version:=xlPivotTableVersion10).CreatePivotTable _

        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _

        :=xlPivotTableVersion10

    Sheets("Sheet1").Select

    Cells(3, 1).Select

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dönem")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Risk Kodu")

        .Orientation = xlRowField

        .Position = 2

    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Kredi Limiti"), "Sum of Kredi Limiti", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("0-12 Ay Risk"), "Sum of 0-12 Ay Risk", xlSum

    With ActiveSheet.PivotTables("PivotTable1").DataPivotField

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("12-24 Ay Risk"), "Sum of 12-24 Ay Risk", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("24+ Ay Risk"), "Sum of 24+ Ay Risk", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Faiz Reeskontu"), "Sum of Faiz Reeskontu", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Faiz Tahakkuku"), "Sum of Faiz Tahakkuku", xlSum

    ActiveWorkbook.ShowPivotTableFieldList = False

    Range("A1:H1178").Select

    Selection.Copy

    Sheets.Add After:=Sheets(Sheets.Count)

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Sheet0").Select

    Application.CutCopyMode = False

    Sheets.Add

   ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache. _

        CreatePivotTable TableDestination:="Sheet3!R3C1", TableName:="PivotTable2" _

        , DefaultVersion:=xlPivotTableVersion10

    Sheets("Sheet3").Select

    Cells(3, 1).Select

    ActiveWorkbook.ShowPivotTableFieldList = True

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Dönem")

        .Orientation = xlRowField

        .Position = 1

    End With

    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _

        "PivotTable2").PivotFields("Üye Kodu"), "Count of Üye Kodu", xlCount

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Üye Kodu")

        .Orientation = xlColumnField

        .Position = 1

    End With

    Range("GS5").Select

    ActiveCell.FormulaR1C1 = "=+COUNTA(RC[-199]:RC[-1])"

    Range("GS5").Select

    Selection.Copy

    Range("GS6").Select

    Range(Selection, Selection.End(xlDown)).Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    ActiveSheet.Next.Select

    ActiveSheet.Next.Select

    Range("I4").Select

    ActiveCell.FormulaR1C1 = "Banka Sayisi"

    Range("I5").Select

    ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-8],Sheet3!R5C1:R1013C201,201,0)"

    Range("I5").Select

    Selection.AutoFill Destination:=Range("I5:I2630")

    Range("I5:I2630").Select

    Cells.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

    Range("D11").Select

    Application.CutCopyMode = False

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$I$2630"), , xlNo).Name = _

        "Table1"

            Cells.Select

    Cells.EntireColumn.AutoFit

    Range("C6:H6").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Style = "Comma"

    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"

    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

    Range("A3").Select

End Sub
 
 
 
Kaynak: http://bankacilaricinmacrolar.blogspot.com.tr/
 

 

  • 1
  • 2
  • 3
  • 4
  • 5
Giriş
Giriş Yap
Yeni şifre gönder