Pivot table macro

[mzsuga]

I don't even know if this is possible or not, I never used a macro on a pivot table before. Is it possible to have a title on the first row based on what is being checked on a report filter in a pivot table? Let's say the report filter is Name and I check Bob, Jenny, and John, can I have those 3 names or however many I select on the first row displayed as Names: Bob, Jenny, John ???

Thanks

[JWhite]

Sure. just loop through all the "pivotitems" to see which ones are "visible". Excuse me if I make a syntax error. I've mostly been coding Visual Basic lately. But it's something like this:

With activesheet.pivottables("pivottablename").pivotfields("Name")
For i = 1 to .pivotitems.count
If .pivotitems(i).visible = true then
TitleString = Titlestring & .pivotitems(i)
Next i
End with

[mzsuga]

Sure. just loop through all the "pivotitems" to see which ones are "visible". Excuse me if I make a syntax error. I've mostly been coding Visual Basic lately. But it's something like this:

With activesheet.pivottables("pivottablename").pivotfields("Name")
For i = 1 to .pivotitems.count
If .pivotitems(i).visible = true then
TitleString = Titlestring & .pivotitems(i)
Next i
End with


This sounds great. But how do I execute it automatically? Like if the person chooses bob and charles it will automatically pop on the first row?

[JWhite]

I don't know how you would capture that specific event (change in selection of pivotitems) but if someone else knows I would like to hear it.

Most of the stuff I do with pivottables is creating them or updating them when the user requests them so I have specific times that I go in and examine the pivottable. Good luck with that.

[mzsuga]

I don't know how you would capture that specific event (change in selection of pivotitems) but if someone else knows I would like to hear it.

Most of the stuff I do with pivottables is creating them or updating them when the user requests them so I have specific times that I go in and examine the pivottable. Good luck with that.


I am trying to find a way but it don't seem to work. Here is what I have so far, if anyone can help, please.



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

Application.Run "'TNT_Winlid.xls'!Automatic_Event"

Dim pt As PivotTable
Dim pi As PivotItems
Dim pf As PivotFields
Set pt = ActiveSheet.PivotTables("PivotTable1")
pt.ManualUpdate = True
Else
With pt.PivotFields("Unit Name")
For i = 0 To pi.Count
If pi(i).Visible = True Then
ActiveSheet.Rows("1:2").Insert Shift:=xlDown
Cells(1, 1).Value = "Name:" & "," & pi(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

'========================================================================== ='
'Prompts Save As


Dim FileSaveAsName As Variant

FileSaveAsName = Application.GetSaveAsFilename( _
InitialFileName:="Name" & Date, _
FileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")
If fileSaveName <> "" Then ActiveWorkbook.SaveAs fileSaveName
End Sub

[mzsuga]

Can anyone please help? I really acn't figure this out


I am trying to find a way but it don't seem to work. Here is what I have so far, if anyone can help, please.




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

Application.Run "'ME_REPORTS.xls'!Automatic_Event"
Dim FileSaveAsName As Variant

ActiveSheet.PivotTables("pivottable1").ManualUpdate = True
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 of Person:" & " " & .PivotItems(i).Name & ","
Cells(1, 1).Font.Size = 13
Cells(1, 1).Font.Bold = True
Range("A1:d1").HorizontalAlignment = xlCenterAcrossSelection
PrintTitleRows = "$1:$5"
End If
Next i

End With

ActiveSheet.PivotTables("pivottable1").ManualUpdate = False


FileSaveAsName = Application.GetSaveAsFilename( _
InitialFileName:="ME" & " " & Date, _
FileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")
If fileSaveName <> "" Then ActiveWorkbook.SaveAs fileSaveName
End Sub