+ Reply to Thread
Results 1 to 2 of 2

Pivot table losing column fields when run from macro

  1. #1
    Registered User
    Join Date
    12-19-2011
    Location
    Leeds, Englean
    MS-Off Ver
    Excel 2007
    Posts
    1

    Pivot table losing column fields when run from macro

    Firstly let me state, I'm not macro literate and certainly not an XL expert.

    However, I want to record a macro which involves creating a simple pivot table and save it in my personal workbook so it's available at any time for the particular set of data I'm looking at.

    The pivot table creation and reording process seems to go fine, displaying the results I want with the resulting Grand Totals at the end of each row.

    However when I run the macro on a fresh set of data, the macro obviously works as does the pivot but there is no data appearing in the "Data Items Area", only the grand totals for the rows!

    This occurs with both 2007 and 2003 versions and for the life of me I can't figure out how to get the macro to work properly and show the data laid out as I had recorded it. It even happens if I run the macro on the original data I had used to create it!

    a copy of the code as follows

    ActiveWorkbook.SaveAs Filename:= _
    "S:\Training Reports\Div Med Dev training.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    Range("A4").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "TrainingByDivisionRiskMatron3!R3C1:R5918C8", Version:=xlPivotTableVersion10) _
    .CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
    , DefaultVersion:=xlPivotTableVersion10
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Work Area Code")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
    .Orientation = xlColumnField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Status"), "Count of Status", xlCount
    End Sub



    Any suggestions would be gratefully received.

    With thanks

    medeng
    Last edited by medeng; 12-19-2011 at 09:37 AM. Reason: missing text

  2. #2
    Registered User
    Join Date
    04-19-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Pivot table losing column fields when run from macro

    Hello Medeng,

    I am having exactly the same issue as you.

    I am hoping that you have found the solution and would kindly share it with me.

    many thanks

    Mikaspike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1