Pasting as an Image and then Re-sizing image

[scotthannaford1973]

Hi

I'm using the code below to basically select a part of a worksheet and paste it as an image to another part of the sheet - which is fine. What I cannot work out is how to include re-sizing - when pasted, I'd like the image to then become 75% of the pasted size. The issue is that when I copy an image it will become "Picture 1" and then the next "Picture 2" - and when I record the macro it will include "Picture 1" in the VB - so if i run it twice, it then errors as the newest image will be called "Picture 2" whereas the VB is thinking it's "Picture 1". Am I able to include code that will select the current image and apply a 75% re-sizing? Thanks in advance!

Sub GenerateLabel()
'
' GenerateLabel Macro
'

'
Range("BB1:BE8").Select
Selection.Copy
Range("D35").Select
ActiveSheet.Pictures.Paste.Select



End Sub

 

[rollis13]

Changing the line to:

ActiveSheet.Pictures.Paste.ShapeRange.ScaleWidth 0.75, msoTrue

will shrink the image you are pasting.

 

[scotthannaford1973]

Changing the line to:

ActiveSheet.Pictures.Paste.ShapeRange.ScaleWidth 0.75, msoTrue

will shrink the image you just pasted.

thanks - I ended up using the code below as decided that it would be just as easy to specify the size of every shape on the page, rather than work out how to just re-size the latest one I also wondered that if I added one shape and include your code... if I then add a new shape and run the VB, would it then make every image 0.75 size every time the button is clicked?!

thanks!

Dim s As Shape
Dim ws As Worksheet
Set ws = ActiveSheet

For Each s In ActiveSheet.Shapes
s.LockAspectRatio = msoFalse
s.Width = 170
s.Height = 80

Next s

 

[rollis13]

As said the code I posted will reduce the shape while running the macro.