pmari
09-05-2011, 06:35 AM
Hi Friends
I am fairly new to VBA. I am using following recorded code for creating Pivot table. but whenever there is a change, even though file name , sheet name and column labels are same, this code is nor working.
can anybody help me out. your help highly appreciated.
Sub INVOICE3()
'
' INVOICE3 Macro
' INVOICE3
'
'
Cells.Select
Range("J1").Activate
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R65536C32", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BRANCH SALES OFFICE")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("QTY2"), "Count of QTY2", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("BASIC VALUE"), "Count of BASIC VALUE", xlCount
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of QTY2")
.Caption = "Sum of QTY2"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of BASIC VALUE")
.Caption = "Sum of BASIC VALUE"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP")
.Orientation = xlColumnField
.Position = 2
End With
Rows("1:18").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Thanks in advance.
I am fairly new to VBA. I am using following recorded code for creating Pivot table. but whenever there is a change, even though file name , sheet name and column labels are same, this code is nor working.
can anybody help me out. your help highly appreciated.
Sub INVOICE3()
'
' INVOICE3 Macro
' INVOICE3
'
'
Cells.Select
Range("J1").Activate
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R65536C32", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BRANCH SALES OFFICE")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("QTY2"), "Count of QTY2", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("BASIC VALUE"), "Count of BASIC VALUE", xlCount
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of QTY2")
.Caption = "Sum of QTY2"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of BASIC VALUE")
.Caption = "Sum of BASIC VALUE"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP")
.Orientation = xlColumnField
.Position = 2
End With
Rows("1:18").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Thanks in advance.