Örnek Dosya Excel Makro İle Tablo Özetleme

gicimi

Yeni Üye
Katılım
18 Haz 2018
Mesajlar
62
En iyi yanıt
0
Puanları
18
Yaş
33
Konum
Ankara
Ad Soyad
Hüseyin Yılmaz
Office Versiyon
Office 2016 Pro
Merhabalar,

Excel Makro İle Tablo Özetleme örnek dosya ektedir.

Kod:
Sub InsertPivotTable()

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")


'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Yıl")
        .Orientation = xlRowField
        .Position = 1
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Ay")
        .Orientation = xlRowField
        .Position = 2
End With

'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Bolge")
        .Orientation = xlColumnField
        .Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Harcama")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0"
        .Name = "Gelir "
End With
'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
End Sub
 

Ekli dosyalar

  • Pivot-Tablo Olusturma.xlsm
    21.1 KB · Görüntüleme: 122
Üst Alt