Snasui Excel Forum

Code: Select all

Sub Macro1()
'
' Macro1 แมโคร
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C1:R42C6", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("ที่")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("มหาลัย"), "นับจำนวน ของ มหาลัย", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ย่อ"), "นับจำนวน ของ ย่อ", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("เกิด")
        .Orientation = xlColumnField
        .Position = 2
    End With
End Sub

Code: Select all

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C1:R42C6", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14

Code: Select all

Sub Macro1()
    Dim dataSource As Range
    With Sheets("Sheet2")
        Set dataSource = .Range("a1", .Range("f" & .Rows.Count).End(xlUp))
    End With
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        dataSource, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=ActiveSheet.Range("a3"), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("ที่")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("มหาลัย"), "นับจำนวน ของ มหาลัย", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ย่อ"), "นับจำนวน ของ ย่อ", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("เกิด")
        .Orientation = xlColumnField
        .Position = 2
    End With
End Sub

Code: Select all

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        dataSource, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=ActiveSheet.Range("a3"), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14

Code: Select all

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        dataSource).CreatePivotTable _
        TableDestination:=ActiveSheet.Range("a3"), TableName:="PivotTable1"