VBA CODE FOR PIVOT TABLE

[pmari]

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.

[Aflatoon]

Not working in what sense? Also, I suggest you add VBA tags to your code to make it more legible.

[pmari]

Hi Aflatoon, thanks for response.

As mentioned any change in column number this code is working.

I need a code, like template, to use any workbook not like recorded one.

[Aflatoon]

If your column headers are different, how would the code know what fields go where in the pivot table?

[pmari]

Hi Friend.

Sorry for the delay. My query is, even if the column names are same, file name is different, or change like no.of col is different then code will not work.

is it possible if the field names are find/same in any work book this code should work. Also needs to be altered. I think my expectation is clear.

[Aflatoon]

I think my expectation is clear.

Not to me, I fear. Perhaps someone with greater understanding will be able to assist you.

[justdriving]

I was also interested to know if above VBA code worked.