VBA, creating a pivot table using Excel Macros

Have a look at this
http://support.microsoft.com/kb/818808
What may be the issue is, that the destination for the pivot table is simply not empty. So try to delete the target area first.

Bembi,

What do you mean delete the target area? I tried changing the characters to 911, but it still did not resolve the problem. I am terrible with computers--sorry for not fully understanding your suggestion.

Below is the code I tried to use.

Thanks for the help!

Yoav

CreateAssormentAnalysis_Step12_PivotTable Macro
' Macro recorded 12/29/2008 by Yoav Bergman
'
'
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DataForCalculations!R2C1:R65536C54").CreatePivotTable TableDestination:= _
        "'[AOT Sample_NEW_v3.xls]Pivot'!R1C1", TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion10
 
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
        "Style_Desc", "Data"), PageFields:=Array("Attribute2", "New/ Update/ Relist", _
        "Brand", "Price Point", "Quadrant", "Novelty/ Basic", "Casual/ Day", "Attribute1", _
        "Attribute 3", "Color Description", "Class Name")
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("TY Sales $")
        .Orientation = xlDataField
        .Caption = "TY Sales ($)"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Unit Demand")
        .Orientation = xlDataField
        .Caption = "Unit Demand (Sum)"
        .Position = 2
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Unit Demand")
        .Orientation = xlDataField
        .Caption = "Unit Demand (Avg.)"
        .Position = 3
        .Function = xlAverage
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Gross Margin Demand $" _
        )
        .Orientation = xlDataField
        .Caption = "Gross Margin Demand (Sum $)"
        .Position = 4
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Gross Margin Demand $" _
        )
        .Orientation = xlDataField
        .Caption = "Gross Margin Demand (Avg. $)"
        .Position = 5
        .Function = xlAverage
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotFields("ST %").Orientation = _
        xlDataField
    Range("C13").Select
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable2").Format xlReport7
    Range("A13").Select
    ActiveSheet.PivotTables("PivotTable2").PivotSelect "Style_Desc", xlButton, True
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Font.Bold = False
    With Selection.Font
        .Name = "MS Sans Serif"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
End Sub

Select allOpen in new window

Hi Yoav,
I do not have a EX2007 system here (outside my office), so I can not directly test your macro. I run into another error with EX2003. Maybe I do this later.
As I can see, you want to create the Pivot table onto the table "Pivot" starting within the field "A1" (R1C1). This cell should be empty, before you run the macro.
If you record a macro, the record procedure creates the target pivot table. If you then run the macro, the pivot table is still present you have created it during recording.
Is AOT Sample_NEW_v3.xls the same Excel Workbook or do you create the Pivot table with data from a different Workbook?

Bembi,

I am actually using Excel 2003 as well. I think I just need to figure out how to clear all data from that Pivot Table Worksheet before I put in the command to insert the Pivot Table. What would this command be?

Just to clarify, I am trying to create a Pivot table into a worksheet called "Pivot" with data from a different worksheet called "DataForCalculations". I think we just need to ensure that A1 in the Pivot table tab is cleared before running th emacro. I think once I figure this out it will work.

Do you know what the command for deleting all existing material (when I try to put in the pivot table in A1 it may be populated by an outdated Pivot Table).

Thanks a lot!

Yoav

   Worksheets("MyWorkSheetname").Range("XX:XX").ClearContents
for a defined range area (ie A1:C5)

    Worksheets("MyWorkSheetname").Cells.ClearContents
for the whole active Worksheet.

When I tried to do that it comes up as "Run-time error '1004" Cannot change this part of a Pivot Table report. Ideas?

It would be great it I could have just recorded building the table and it would have worked.

Thanks,

Yoav

Bembi,

I tried deleting the lines but it did not work. I am trying to create a pivot table from the DataForCalculations Tab. The macro that I tried using is attached, but for some reason it is not working.

Thanks for the help!

Yoav
Pivot-Table.xls

Here is the most updated Pivot_Table file. Same question as above--I would be forever grateful if you could figure out how to make it work.

Thanks!

Yoav
Pivot-Table.xls

OK, here is the first sequence, this runs in the english version.

Changes
I activated the Pivot target Sheet, as this has to be active
I used Delete instead of ClearContents

SourceData:= "DataForAnalysis!R2C1:R100C54")
My file had a different Sheet name, so I corrected this to the correct name
Also I shortened the range, make sure your name and range is fitting your needs
(your range was about row 65000)
This works for me in EX2003 and EX2007

Additional notes
Try to avoid common names as you may hit protected words. "Pivot" works in that case, but if these names are used internal by Excel, you get unspecific errors.
For field names, try to uses database conform names, avoid special characters. This is sometimes a source of error. For the Pivot table, this works.

    Worksheets("Pivot").Activate
    Worksheets("Pivot").Cells.Delete
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DataForAnalysis!R2C1:R100C54").CreatePivotTable TableDestination:= _
        "Pivot!R1C1", TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion10

Select allOpen in new window