how can i create a macro for creating a pivot table.

Hi,

I have data in sheet1 called "Daily data" and in the next sheet the pivot should be create and there starts my problem.... I have to put "Region","Function",and "Manager" in the row fileds of Pivot and in column the count.

I have to put filter on Row Pivot Fileds for Region and unselect all feilds execpt "Stamford" and likewise for Function only "Repo".

I Have no idea but just recorded a macro is giving an error.

Please help me out ..would be Highly Appreciated.

Sub BES()
Dim pt As PivotTable
Dim pi As PivotItem
    Cells.Select
      ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Daily Data'!C2:C17").CreatePivotTable TableDestination:="", TableName:= _
        "my pivot", DefaultVersion:=xlPivotTableVersion10
    Set pt = ActiveSheet.PivotTables("my pivot")
     ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("my pivot").PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("my pivot").PivotFields("GCMM  Function")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("my pivot").PivotFields("Collateral Manager ")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("my pivot").AddDataField ActiveSheet.PivotTables( _
        "my pivot").PivotFields("Ref Ccy"), "Count of Ref Ccy", xlCount
    With ActiveSheet.PivotTables("my pivot").PivotFields("Region")
        .PivotItems("Houston").Visible = False
        .PivotItems("London").Visible = False
        .PivotItems("Singapore").Visible = False
        .PivotItems("Tokyo").Visible = False
        .PivotItems("Zurich").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("my pivot").PivotFields("GCMM  Function")
        .PivotItems("OTC").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("my pivot").PivotSelect _
        "'GCMM  Function'[All;Total]", xlDataAndLabel, True
    Selection.Delete
    Range("A9:D10").Select
    Range("D9").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Range("A3:D4").Select
    Range("D4").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Range("A9:D10").Select
    Range("D10").Activate
    Selection.Font.Bold = True
    Range("A3:D4").Select
    Range("D4").Activate
    Selection.Font.Bold = True
    Range("G7").Select
End Sub

Thanks for any Help....