Creating a pivot table automatically

[t16turbo]

I have recorded the steps I used to create a pivot table.

If I then insert a blank worksheet and run the code, I get an error "add fields method of pivot table class failed"

here's the code

ActiveWindow.SmallScroll Down:=-33
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=-9
    Application.CommandBars("Stop Recording").Visible = False
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=-15
    ActiveWindow.SmallScroll ToRight:=22
    ActiveWindow.LargeScroll ToRight:=-2
    Range("D4:D5").Select
    Range("D5").Activate
    ActiveWindow.SmallScroll Down:=510
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=-12
    Range("AD1").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "TotalCombined!C1:C31").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable3"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array( _
        "Sheam Type", "Sheam Desc", "Data")
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("NOV05")
        .Orientation = xlDataField
        .Caption = "Sum of NOV05"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("DEC05")
        .Orientation = xlDataField
        .Caption = "Sum of DEC05"
        .Position = 2
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("JAN06")
        .Orientation = xlDataField
        .Caption = "Sum of JAN06"
        .Position = 3
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("FEB06")
        .Orientation = xlDataField
        .Caption = "Sum of FEB06"
        .Position = 4
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("MAR06")
        .Orientation = xlDataField
        .Caption = "Sum of MAR06"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel

    ActiveSheet.PivotTables("PivotTable3").Format xlClassicPivotTable
    Application.CommandBars("PivotTable").Visible = False

does anyone know why this is not working?

in addition, where the name sof the data fields are mentioned, I want the pivot to get the name of the field from the source data worksheet in specified columns (row 1 of course)

can anybody help?

 

[SkipVought]

Hi,

Your SOURCE DATA is defined as ...

TotalCombined!C1:C31

ONE COLUMN, 31 rows.

Yet you are adding THREE fields.

Where are they coming from!?


Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[t16turbo]

I'm not sure why it said that.
I just recorded te pivot table wizard, which automatically selected the range.

my source data uses Column A to AE.
Cells T1 to AE1 contain Months (Nov05, Oct05 etc)
each month the data s refreshed and T1 becomes a new month.

so, when this process is run next month, T1 will contain Dec05, U1 wil contain Jan06

I need to automaticaly create the pivot to select the entire range (probably by defining the name as a range)
but I need to make it use the values of the Cells in T1 through to AE1 (as a simple refresh, will throw up an error from one month to the next where the values don't tie up...

phew..

 

[SkipVought]

Your MAIN problem is the format of your data!!!

If your souce data were normalized, (for instance, a column for ALL dates, instead of a column for each SEPARATE date), you could get your pivot table report in a matter of SECONDS.

As a programmer, you ought to be able to understand that concept. Clean up your data and the reporting task becomes routine.

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[t16turbo]

Hi Skip,
I can see what you are saying - and have used the wizard to clean up my data.

the problem is,

I am using several spreadsheets that are created by other people (the project managers) in the non-normalized format.
To normalize the combined spreadsheet (all Project Managers' spreadsheets in one sheet) I would also need to write a macro to do that.

Once that has happened, there are several other columns that are in the spreadsheet - so the final pivot table will need these un-checked in the drop down - is this easily done using VBA?

 

[SkipVought]

I don't see a problem.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}