How to add a company tagline (logo) on the row after the last row of a pivot table

[anya1]

I have a pivot table report based on a dynamic named range & allows user to expand row labels to view data. The default setting that the reports will be send out spans over approximately 50 rows but with the expanded data , it could be well over 150 rows.
My company prefers to use a template to generate all reports and the company tagline which contains 3 words, is to be placed on all reports on the right bottom corner of the visible worksheet.
My problem is there's no saying how many rows of data a user might choose to expand to, I can't decide where to place this tagline.Any suggestions on how I can deal with the problem?

[DonkeyOte]

If the Tagline were required only for Printing you could perhaps put this in the Custom Footer ?

Otherwise I think you're looking at VBA - using the Pivot Table update event to place the Tagline accordingly - obviously requires your users enable macros etc...

Caveat to above would be if the Pivot never expands horizontally and you're happy to have Tagline in the column to the immediate right to that of the bottom right corner of the Pivot ?

[anya1]

Hi, thanks for that. The pivot table expands horizontally too, so pasting it to the right isn't an option either.

I can organise for the users to enable macros, but I don't have much exposure to macros & don't know where to start.

[DonkeyOte]

Very basic example:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Const cTagLine = "Merry Christmas"
    Dim rngX As Range
    Set rngX = Cells.Find(cTagLine, LookIn:=xlValues)
    If Not rngX Is Nothing Then
        rngX.Clear
    End If
    With Target.TableRange1
        .Offset(.Rows.Count + 1, .Columns.Count - 1).Resize(1, 1).Value = cTagLine
    End With
    Set rngX = Nothing
End Sub

To utilise above - right click on sheet name containing the Pivot of interest and select View Code - paste above into resulting window and then close VBE.

When the Pivot is refreshed so the TagLine should be added to the cell two rows below the bottom right cell of the Pivot wherever that may be post-refresh.

[anya1]

Brilliant, it worked! Thanks heaps.All i now need to do is format the tagline in the code and add the version no. on the bottom left of the table. I'll keep you posted.

[anya1]

Hi Donkeyote,

The macro runs fine when there's only a few words in the tagline just like 'Merry Christmas" in your example. However, I also need to add the version of the report on the bottom left of the pivot table , (same row as the tagline) . e.g.Version 1.a and then |Very|Merry|Christmas on the right hand bottom. The 'Version1.a' is to be in Tahoma - 10 Regular & '|Very|Merry|Christmas' has to be Tahoma 10 , Bold.I thought I could tweak your code to suit this, but couldn't quite manage to get the results I expected.

Hopefully you're able to help out with this.

[DonkeyOte]

In a bit of a rush I'm afraid so not very elegant but if I've understood perhaps:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim rngX As Range
    Dim vTags As Variant
    Dim bTag As Byte
    vTags = Array("Version1.a", "Very|Merry|Christmas")
    For bTag = LBound(vTags) To UBound(vTags) Step 1
        Set rngX = Cells.Find(vTags(bTag), LookIn:=xlValues)
        If Not rngX Is Nothing Then
            rngX.Clear
        End If
    Next bTag
    With Target.TableRange1
        With .Offset(.Rows.Count + 1)
            .Font.Name = "Tahoma"
            .Font.Size = 10
            .Cells(1).Value = vTags(0)
            With .Cells(.Columns.Count)
                .Value = vTags(1)
                .Font.Bold = True
            End With
        End With
    End With
End Sub