In Excel 97-2003, I had created in a macro to create a pivot table from a
range of data. However, when I converted the file to Excel in Office 2007, I
get VBA errors.
The converted workbook is now an xlsm file with macros enabled.

Not sure what else to do. The VBA error is:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Loans!R8C2:R416C55",
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion12

Steve,

Obviously, I don't have your data set to test the PivotTable, but I used
some mock data and didn't receive an error (i.e. I used your code and
replaced the appropriate arguments). I would try your code again, or you can
reference some illustrative code below which utilizes the object module to
create the PivotTable.

Best,

Matthew Herbert

Sub SampleCreatePivotTable()
Dim Wkb As Workbook
Dim Wks As Worksheet
Dim pvtCache As PivotCache
Dim pvtTbl As PivotTable
Dim rngSourceData As Range

Set Wkb = ThisWorkbook
'you can use .CurrentRegion or combination of .End
' to set the source data range
With Wkb
Set rngSourceData = .Worksheets(1).Range("A1:C10")
Set Wks = .Worksheets.Add
End With

With Wkb
Set pvtCache = .PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=rngSourceData, _
Version:=xlPivotTableVersion12)

Set pvtTbl = pvtCache.CreatePivotTable(TableDestination:= _
Wks.Range("A1"))

With pvtTbl
With .PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With


With .PivotFields("File Name")
.Orientation = xlColumnField
.Position = 1
End With

.AddDataField .PivotFields("Value"), "Sum of Value", xlSum
.RowGrand = False
.ColumnGrand = False
End With

.ShowPivotTableFieldList = False
End With

End Sub


"Steve C." wrote:

In Excel 97-2003, I had created in a macro to create a pivot table from a
range of data. However, when I converted the file to Excel in Office 2007, I
get VBA errors.
The converted workbook is now an xlsm file with macros enabled.

Not sure what else to do. The VBA error is:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Loans!R8C2:R416C55",
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion12