Macro: rename a

[blaksnm]

Hey guys
In my report (in sheet1) I have a bmp-paste (among other types of informations) of at pivottable (of a range).
When updating the pivot-table it could expand - but the bmp just show the original range of the copied range. I risk to miss some lines in the bottom of the expected picture.

I use the copy/paste picture technique because this aibles me to organize the report-appearance independent of rowheight/columnwiths

To achieve my composition of the report -based on different pictures I think I have to 1) delete the bmp-pasting, 2) copy the whole new shape/range of the pivottable and 3) paste the new bmp-picture into its regular place in the report scheeme/composition.

(is this explination understandable?)

When recording this operation I get this (my question/comments in red):

Sub Makro3()
Sheets("Report").Select
'Comment: How will my macro identify this picture next time when the picture-name (see below) is something else (ex Picture 58) - in order to delete it?

ActiveSheet.Shapes("Picture 57").Select
Application.CutCopyMode = False
Selection.Delete
ActiveSheet.PivotTables("Report").PivotSelect "Discipline[All]", xlLabelOnly, _
True
Range("S8:V33").Select ' This should be a dynamic range in any case of the size of the whole pivot-table
Selection.Copy
Range("I3").Select
ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub

Any good advices?
Ill be happy to be guided

 

[AlphaFrog]

Try something like this.

The macro automatically deletes the current picture called ReportPic and then paste a new picture and names it ReportPic.

Before the First time you run this, you have to manually rename your current picture as ReportPic. Select the picture and then type in the name in the Name Box just to the left of the Formula Bar and ENTER.

Sub Makro3()
    With Sheets("Report")
        .Shapes("ReportPic").Delete
        .PivotTables("Report").PivotSelect "Discipline[All]", xlLabelOnly, True
        .Range("S8", .Range("V" & Rows.Count).End(xlUp)).Copy
        .Range("I3").Select
        .Pictures.Paste(Link:=True).Select
        Selection.Name = "ReportPic"
    End With
End Sub

 

[blaksnm]

Thanks a lot - it worked perfectly!