Finding last row under a pivot table and inserting an image.

[andi1987uk]

Hi All,

I’m new to the forum and a total novice at vba although a big fan of excel! I’ve been searching for a solution for the above for a while now and can’t find anything that helps...

The title say it all, I have a sheet that has a pivot table that can obviously increase in rows depending upon the data in the reference table, what I’m trying to do is have an image insert automatically let’s say 1-2 rows below the pivot after a refresh for example. Is this even possible?

I’m using Excel 365, and I believe it is 2016.

I’ve even searched this forum but couldn’t find anything that I’m trying to achieve.

Thanks in advance for any help and please remember I’m a total novice with this lol.

Andi

 

[Domenic]

If I understood you correctly, you have a pivottable on a worksheet, and you also have an image on that same worksheet. Also, you'd like the image to be re-positioned a couple of rows below the pivottable whenever the pivottable is filtered or refreshed. If this is correct, let's assume that the image is named "Picture 1". Then, right-click the sheet tab, select View Code, and copy/paste the following macro in the code module for the sheet...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    With Target.TableRange2
        With .Resize(1, 1).Offset(.Rows.Count + 2, 0)
            Me.Shapes("Picture 1").Left = .Left
            Me.Shapes("Picture 1").Top = .Top
        End With
    End With
End Sub

Change the name of the image accordingly.

Hope this helps!

 

[andi1987uk]

Thanks for this,

I was also hoping to insert the image from vba based on a the data in another cell from another sheet, but i suspect this will be far ti advanced for me.

I’ll try this solution

 

[Domenic]

The following code will allow you to insert an image, 2 rows below the pivot table. Also, the image will be re-positioned appropriately whenever the pivot table is filtered or refreshed.

The code assumes that Sheet1 contains the pivot table, and that Sheet3!A1 contains the path and filename of the image to be inserted below the pivot table. Change these references accordingly.

First, copy/paste the following code into a regular module (Visual Basic Editor >> Insert >> Module)...

Option Explicit

'set the image name to myImageName (change as desired)
Public Const IMAGE_NAME As String = "myImageName"


Sub InsertPivotTableImage()


    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pathAndImageFilename As String
    
    'set the path and filename for the image (change worksheet name and cell reference accordingly)
    pathAndImageFilename = Worksheets("Sheet3").Range("a1").Value
    
    'validate the path and filename
    If Len(pathAndImageFilename) = 0 Then 'empty cell
        MsgBox "Missing path and filename!", vbExclamation
        Exit Sub
    ElseIf Len(Dir(pathAndImageFilename, vbNormal)) = 0 Then 'invalid path and filename
        MsgBox "Invalid path and filename!", vbExclamation
        Exit Sub
    End If
    
    'set the worksheet containing the pivot table (change the sheet name accordingly)
    Set ws = Worksheets("Sheet1")
    
    'set the pivot table (change the pivot table name accordingly)
    Set pt = ws.PivotTables("PivotTable1")
    
    'delete image, if one by the same name already exists
    On Error Resume Next
    ws.Shapes(IMAGE_NAME).Delete
    On Error GoTo 0
    
    'insert image 2 rows below the pivot table
    With pt.TableRange2
        With .Resize(1, 1).Offset(.Rows.Count + 2, 0)
            ws.Shapes.AddPicture(pathAndImageFilename, msoFalse, msoTrue, .Left, .Top, -1, -1).Name = IMAGE_NAME
        End With
    End With
    
    'clear from memory
    Set ws = Nothing
    Set pt = Nothing
    
End Sub

Then, copy/paste the following code into the code module for the sheet containing the pivot table, which in this case would be Sheet1 (right-click the sheet tab, select View Code)...

Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


    Dim myShape As Shape
    
    're-position the image 2 rows below the pivot table, if image exists
    On Error Resume Next
    Set myShape = Me.Shapes(IMAGE_NAME)
    If Not myShape Is Nothing Then
        With Target.TableRange2
            With .Resize(1, 1).Offset(.Rows.Count + 2, 0)
                myShape.Left = .Left
                myShape.Top = .Top
            End With
        End With
    End If
    On Error GoTo 0
    
End Sub

Here's a sample workbook...

https://1drv.ms/x/s!Atmin3kqXqL0cJVd69MmGrh8r8g

In the sample workbook, make sure that you change the path and filename in Sheet3!A1 accordingly.

Hope this helps!

 

[andi1987uk]

That’s great thanks so much for the code and you clear explanation!