Pivot macro

[rvkcools]

Hi fnds,

I would like to create a vba cobe for a pivot table.

i have a data in a sheet1 and i want to create pivot table in sheet2

in sheets1 i have a filed in A1 filed as name, b1 filed as location, c1 field as qty, d1 field as region, f1 field as product.

Please help me

 

[carddard]

Hi rvkcools,

It would help a lot if you could attach a sample file.

 

[rvkcools]

https://docs.google.com/file/d/0B2QpdKxyYresUW5YaGMySGlvdUE/edit?usp=sharing

please find the attached link and u can choose ur filed for pivoting

 

[par60056]

The sample does not match what you described.

Is there a reason that you need it to be a macro to make the pivot table? If this is something that you need to automate in a way other than just refreshing the data, then I find the best way to learn about coding a pivot table is to record doing it. The code written by the recorder is not great but it will show you the steps and you can easily modify it to make it more generic for repeated use.

 

[rvkcools]

its a dump file. where i want to automate the pivot by writing a prog with refreshing the data sheet on sheet1

can you please do it on ur own code?

Thanks

 

[par60056]

Here is a simple piece of code so you can see how a pivot table is build.

You will need to figure out what fields you want to pivot on and what you need to total.

    Sheets.Add.Name = "Sheet4"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R75C11", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet4").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum