Hi,

The data in the different sheets of my workbook have all the same
headings. To ease the analysis of the data, I would like to design a
code that creates the pivot table for the 'Active Worksheet'
respecitively.
I evolved a macro for one sample but it is not possible to assign it to
the other sheets due to error 1004.
As I am not very familiar with VBA I do not know what to change to get
the working code for all sheets in the workbook and for all different
workbooks accordingly.

Here the code:

tablename = Right(ActiveSheet.Name, 1)


Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"'Detail A" & tablename & "'! c1:c23 ").CreatePivotTable
TableDestination:="", tablename:="Pivot " & tablename
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Pivot " &
tablename).PivotFields("Doc.no.")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Pivot" &
tablename).PivotFields("Category2")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Pivot" & tablename).AddDataField
ActiveSheet.PivotTables( _
"Pivot" & tablename).PivotFields("Part"), "Count of Part",
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Error 1004 occurs in line:
With ActiveSheet.PivotTables("Pivot " &
tablename).PivotFields("Doc.no.")

Thanks
Alex