Programming a macro in Excel Office 2007 to create Pivot Table

S

Steve C.

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:=xlDatabase, SourceData:= _
"Loans!R8C2:R416C55",
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion12
 
M

Matthew Herbert

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top