PivotTable

ibrahimaa · #1 04-14-2012, 11:17 AM

Are there codes that I can use in a macro to create a PivotTable? I mean to create the column and row labels and the value fields?

I appreciate your support. Thank you.

ibrahimaa · #2 04-15-2012, 12:30 AM

I have find a way to resolve this issue. I have created a PivotTable as we usually do and I have recorded all the steps in a macro which I am using now in different places. Below is the code. Thank you.

Code:

Sub Macro3()
' Macro3 Macro
    Workbooks.Open Filename:= _
        "D:\TERMINATIONS.xlsx"
    Sheets("Terms").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("H1:H18").Select
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 30
      Range("H1:H627").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:= _
        "Terms!R1C8:R627C8", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Emp Subgrp Desc")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveWorkbook.save
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy Before:=Workbooks("1575Examples.xlsm").Sheets(4)
    ActiveWorkbook.save
    Windows("TERMINATIONS.xlsx").Activate
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveWindow.Close
    Range("E17").Select
    ActiveWorkbook.save
End Sub