Snasui Excel Forum

Code: Select all

sub test()
On Error Resume Next
Dim i As Integer

Application.ScreenUpdating = False
    Sheets("data").Select
Range("a1").Select
Do While Not IsEmpty(ActiveCell.Value)
 i = i + 1
 ActiveCell.Offset(1, 0).Select
Loop

    Range("A1").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="data!R1C1:R" + CStr(i) + "C71").CreatePivotTable TableDestination:="", TableName:="PivotTable1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("mt_r", _
        "gl_group"), ColumnFields:=Array("sector", "sectoren"), PageFields:= _
        "mapping_no"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("mt_t")
        .Orientation = xlDataField
        .NumberFormat = "#,##0.00"
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("mapping_no").CurrentPage = _
        "BAU Expense"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("B2").Select

end sub

Code: Select all

On Error Resume Next
Dim i As Integer
Dim j As Integer

Application.ScreenUpdating = False
Sheets("data").Select
Range("a1").Select
Do While Not IsEmpty(ActiveCell.Value)
 i = i + 1
 ActiveCell.Offset(1, 0).Select
Loop

For j = 1 To i
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="data!R1C1:R" + CStr(i) + "C71") _
    .CreatePivotTable TableDestination:="", TableName:="PivotTable" & j
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable" & j).SmallGrid = False
    ActiveSheet.PivotTables("PivotTable" & j).AddFields RowFields:=Array("mt_r", _
        "gl_group"), ColumnFields:=Array("sector", "sectoren"), PageFields:= _
        "mapping_no"
    With ActiveSheet.PivotTables("PivotTable" & j).PivotFields("mt_t")
        .Orientation = xlDataField
        .NumberFormat = "#,##0.00"
    End With
    ActiveSheet.PivotTables("PivotTable" & j).PivotFields("mapping_no").CurrentPage = _
        "BAU Expense"
    Cells.Select
    Cells.EntireColumn.AutoFit
Next j

Range("B2").Select