Pivot Table Cell reference and Macro

Topic

New Reply

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:=xlDatabase, 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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Do any of the pivot table autoformats meet the formatting you want?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote