Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Cell reference and Macro

    Hi
    Re: Excel 2003

    I have just started utilising Pivot Tables so am very new to it, however I have set up one table and have then recorded a macro so that I can run the macro to do the table whenever I have a new report. The contents of the reports are the same however the amount of data varies.

    The issue I have is that in the macro it identifies the cell ranges, however all my reports are different sizes. Is it possible to replace the cell range with a reference??? so that it automatically identifies the whole table regardless of size? See macro below.

    Also while I'm on a roll, is it possible to include in the macro the option to bold and change the colour of the data in row and or column totals ?

    Thanks in advance for your assistance

    Cheers
    Dax


    Sub Pivot_report()
    '
    ' Pivot_report Macro
    ' Macro recorded 15/04/2011 by KuilboFY
    '
    ' Keyboard Shortcut: Ctrl+p
    '
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    "contract_extract!R1C1:R28C22").CreatePivotTable TableDestination:="", _
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("familyname").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("firstname").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("client_id" _
    , "familyname", "firstname", "course_id", "module_id"), ColumnFields:= _
    "modoutcome"
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("module_hrs_super"). _
    Orientation = xlDataField
    End Sub
    Last edited by dax1956; 2011-04-14 at 21:58.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Something like:
    Code:
    Sub Pivot_report()
    '
    ' Pivot_report Macro
    ' Macro recorded 15/04/2011 by KuilboFY
    '
    ' Keyboard Shortcut: Ctrl+p
    '
       Dim PC As PivotCache
       Dim PT As PivotTable
       Dim strData As String
       Dim wks As Worksheet
       
       ' add new sheet for pivot table
       Set wks = Sheets.Add
       
       ' Use the region round A1, whatever that may be
       strData = Sheets("contract_extract").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
       
       ' add cache
       Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                         SourceData:="contract_extract!" & strData)
       ' add pivot table
       Set PT = PC.CreatePivotTable(TableDestination:=wks.Cells(3, 1), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10)
       
       ' set up pivot
       With PT
          .PivotFields("familyname").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
          .PivotFields("firstname").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
          .AddFields RowFields:=Array("client_id", "familyname", "firstname", "course_id", "module_id"), ColumnFields:="modoutcome"
          .PivotFields("module_hrs_super").Orientation = xlDataField
       End With
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory
    Thanks very much for the updated code, works exactly how I was wanting it to.

    Was wondering if I could ask for your help with adding some code so that each each total row and column is coloured and the data is in bold.

    Any chance you could assist with this....thanks

    Cheers
    Dax

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Do any of the pivot table autoformats meet the formatting you want?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Do any of the pivot table autoformats meet the formatting you want?
    Hi Rory

    Again thanks for your help, Yes the auto format function partly assisted with my formatting requirements.

    The other component that I still am trying to work out is how to automatically format a particular column so that the text colour is changed to red and is bold. The problem I have is that the column will only appear if there is data for that particular column.

    Any suggestions??

    Cheers
    Dax

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Could you post a sample so I can see exactly what you mean? Are you talking about a whole column field, or a particular item in the field?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory

    I've attached a sample sheet as requested.

    My issue is that with some of my reports under the 'modoutcome' column, there are a range of outcome codes that could appear (20, 30, 40, 90 etc). Most are ok however if the modoutcome code 90 comes up for any client, this needs to be addressed.

    To ensure this is addressed I thought I could include in the macro for the pivot table an option that if the 90 code comes up any data in that column under the 90 code would be in red and bold text.

    Trust this makes sense?

    Thanks again for yur assistance and patience

    Cheers
    Dax
    Attached Files Attached Files

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Something like this:
    Code:
       Dim pt As PivotTable
       Set pt = ActiveSheet.PivotTables(1)
       On Error Resume Next
       With pt.ColumnFields("modoutcome").PivotItems("90").DataRange.Font
          .ColorIndex = 3
          .Bold = True
       End With
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory

    Once again thanks very much for your help and especially your patience with this. It's now working just how I need it.

    Cheers

    Dax

Posting Permissions

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