Programming a macro in Excel Office 2007 to create Pivot Table
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 |
Programming a macro in Excel Office 2007 to create Pivot Table
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 |
All times are GMT +1. The time now is 02:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com