Pivot Table Macro Help 1

[michaela18]

Hi,I am trying to get this to work automatically. It is a pivot table and on the top there is a report filter field "Name of Person". I run a report everytime and I always choose different names, like Bob and Gary, or Cindy and Sonia. So everytime I select a diff name I want the names selected to appear as the title in cell A1 so when I print it, I can see what I chose. But it isn't working. Help please.








Private Sub Automatic_Event(ByVal Target As Range)
If Intersect(Target, PivotFields("Name of Person")) Is Nothing Then
Exit Sub

'==========================================================================='

'Inserts the Title and Formats it '

Else
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name of Person")
For i = 1 To .PivotItems.Count
If .PivotItems(i).VisibleFields = True Then
ActiveSheet.Rows("1:2").Insert Shift:=xlDown
Cells(1, 1).Value = "Name:" & "," & PivotItems(i)
Cells(1, 1).FontSize = 13
Cells(1, 1).Font.Bold = True
Range("A1:d1").HorizontalAlignment = xlCenterAcrossSelection
.PrintTitleRows = "$1:$6"
Next i


End With
End If

'==========================================================================='

Dim FileSaveAsName As Variant

FileSaveAsName = Application.GetSaveAsFilename( _
InitialFileName:="Name"
FileFilter:="Excel Macro-Enabled Workbook (*.xls), *.xls")
If fileSaveName <> "" Then ActiveWorkbook.SaveAs fileSaveName




End Sub

 

[SkipVought]

Hi,

...on the top there is a report filter field "Name of Person".

'on the top' mean WHAT? Is this a PAGE field? Is it a ROW field? Is it a COLUMN field?

I'd do it differently, though. I'd make a List of the Names and make A1 a Data > Validation -- LIST dropdown and drive the Pivot filter from the WorksheetChange event when A1 changes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[michaela18]

'on the top' mean WHAT? Is this a PAGE field? Is it a ROW field? Is it a COLUMN field?

I'd do it differently, though. I'd make a List of the Names and make A1 a Data > Validation -- LIST dropdown and drive the Pivot filter from the WorksheetChange event when A1 changes.

It is not a page field nor a row field, it is a Report filter field, you know on the top. It is not dragged into the pivot table. I don;t know how to do it your way. The Name of Person field is on A1 and when I choose different names, the pivot table changes to reflect those names. Any tips?

 

[combo]

When you display pivot table wizard, under 'option' you can see a pivot table pattern with four areas, there's page area on the top-left, every field here is a page field.

Concerning the automatic updating, it's not quite clear for me what you intend to do. In case of simple referencing, you can create a formula [tt]="Name: "&A10[/tt] (if the field is in A10) or just [tt]=A10[/tt] and use custom number format [tt]"Name: " @[/tt].

If you need to automatically select item by item and the 'filter' is in the page field, you can:
[tt]With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name of Person")
For i = 1 To .PivotItems.Count
.CurrentPage = .PivotItems(i).Value[/tt]





combo