Link to home
Start Free TrialLog in
Avatar of RWayneH
RWayneHFlag for United States of America

asked on

Pivot not working.

For some reason this Pivot is not working.  Any ideas why this would not preform the way it was recorded?  It should create a sheet tab "Sheet5" everytime and it is not.  There are four other sheet tabs in this file.  Thanks  -R-


Sub Pivot1()

    Columns("A:A").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Open Orders!R1C1:R1048576C1", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:="PivotTable8" _
        , DefaultVersion:=xlPivotTableVersion12
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("SOSOLN")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("SOSOLN"), "Count of SOSOLN", xlCount

Open in new window

Pivot1.xlsx
Avatar of als315
als315
Flag of Russian Federation image

Try to change
Sheets.Add
to
Sheets.Add.Name = "Sheet5"
Avatar of RWayneH

ASKER

No luck... it creates the sheet tab fine... it however does not put the SOSOLN in the rows of the pivot table, only in the column as a Count... -R-
Your question was:
It should create a sheet tab "Sheet5" everytime and it is not.
Do you like to get on inserted sheet pivot table from sheet6 in your sample?
Avatar of RWayneH

ASKER

If I create the workbook in sequence it does create Sheet5, even with your edit... however look at what the pivot looks like.... it does not honor the SOSOLN into the rows... as the recorder does it.  Something is going on in the using of the same field as rows and values..

Please run the code and you will see what I mean.  (at least is has not been  working for me).
PivotError.jpg
Can you show expected result?
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RWayneH

ASKER

EXCELlent!!  Thanks. -R-