Copy Image from Clipboard into Excel in Certain Cells

[ashani]

Hi everyone,

I'm using the below formula and I want to paste image from clipboard in certain area - however when i do it, the image doesn't fit in that section properly and either shrinks so much or so little.

Also, if there is no image on the clipboard or text - then I want the message box pop up to say "nothing to copy".

Thank you for your help.

Sub copy()
    Dim p As Picture
    Dim Target As Range
    
    Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="abc"

    Set p = ActiveSheet.Pictures.Paste
    Set Target = Range("B3:I26")
       
    With Target
        p.ShapeRange.LockAspectRatio = msoFalse 'Allows Proper Sizing
        p.Top = .Top
        p.Left = .Left
        p.Width = .Width
        p.Height = .Height
    End With

ActiveSheet.Protect Password:="abc", DrawingObjects:=False, contents:=True, Scenarios:=True


End Sub

 

[ashani]

you're amazing @rlv01 and the best - thank you so much
just one question on this - is it possible to have date & time of the copy next to the image or underneath - once click macro button it paste the object and put date & time ?
Thank you once again.

 

[rlv01]

Code to do that is included:

        With .Offset(.Rows.Count, 1).Resize(1, 1)     'modify to suit yourself
            .Value = CStr(VBA.Date & "   " & VBA.Time)
            .EntireColumn.AutoFit
        End With

but where it should be and how it should be formatted is so subjective that I think you need to experiment with it.

 

[ashani]

agree but for some reason it's not coming up. Here is the screenshot - I'd like a date and time of paste in Column A11 for example.

 

[rlv01]

    ActiveSheet.Range("A11").Value = CStr(VBA.Date & " " & VBA.Time)

 

[ashani]

You're the best - thank you so much for all your help.

 

[rlv01]

Glad you got it working.