Copy whole sheet to clipboard as picture when sheet protected

MarkieVBA

New Member
Joined
Nov 4, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have some simple code to copy a sheet (an invoice form) as a picture to the clipboard for pasting into Outlook emails through a command button:

Sub ButtonCopy()
ActiveSheet.Range("A1:DG182").CopyPicture xlScreen, Format:=xlPicture
ActiveSheet.Paste
Selection.Width = 500
Selection.Height = 500
Selection.Copy
Selection.Delete
MsgBox "Invoice copied to clipboard." & vbNewLine & "" & vbNewLine & "Please paste (Rt Click or CTRL+V) in your existing email (with documents attached) from the supplier/office"
End Sub

It works exactly how I want it BUT only if the 'edit objects' option of the Protect Sheet function is unticked (which leaves the command buttons editable and a backdoor to the VBA which is bad news).

When ticked and fully protected it produces runtime error 1004 .. 'cannot paste the data' and the debugger highlights the 'ActiveSheet.Paste' line.

I've seen similar examples online but they all seem to deal with pasting the sheet into another sheet which doesn't help. I also tried using 'On Error Resume Next' and 'Application.CutCopyMode = False' which did allow me to fully protect the sheet without the 1004, but it only copied the currently highlighted box as a picture.

I feel the solution is close, but I've run out of ideas. Can anyone please help?

Many thanks in advance.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
I've seen similar examples online but they all seem to deal with pasting the sheet into another sheet which doesn't help.

Have you considered using a temporary worksheet? After all, you can easily remove it afterwards, like this:

VBA Code:
Sub ButtonCopy()

    ActiveSheet.Range("A1:DG182").CopyPicture xlScreen, Format:=xlPicture

    Application.ScreenUpdating = False
    With Workbooks.Add.ActiveSheet
        .Paste
        With .Shapes(1)
            .Width = 500
            .Height = 500
            .Copy
        End With
        .Parent.Close SaveChanges:=False
    End With
    Application.ScreenUpdating = True

    MsgBox "Invoice copied to clipboard." & vbNewLine & "" & vbNewLine & "Please paste (Rt Click or CTRL+V) in your existing email (with documents attached) from the supplier/office"
End Sub
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Glad to help and welcome to this board!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,227
Messages
5,576,844
Members
412,749
Latest member
BlakeVanderMeer
Top