It is the Application.CutCopyMode = False that is clearing the clipboard,
not the re-protecting of the sheet.
Remove that line then run the macro.
Bring up the clipboard after macro ends and see what's available to paste.
Do not try to use right-click>paste or edit>paste.
You must use the clipboard.
You could open Outlook and paste the clipboard contents into an email.
You can shorten the macro a bit.
Sub myname()
ActiveSheet.Unprotect
Range("A1").Select
ActiveCell.CurrentRegion.Copy
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True _
, AllowFiltering:=True
End Sub
As an aside............Take a trip to Ron de Bruin's site for all you need
to know about sending emails See his SendMail add-in.
http://www.rondebruin.nl/sendmail.htm
Gord
On Wed, 2 Jun 2010 17:17:14 -0700, Travis Patterson
<(E-Mail Removed)> wrote:
>Thanks Gord,
>
>After running the "copy data macro" My colleage will paste the data intoan
>email he sends out to our team members. The copy data macro is simple:
>
>Sub
> ActiveSheet.Unprotect
> Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.Copy
> Application.CutCopyMode = False
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
>Scenarios:=True _
> , AllowFiltering:=True
>End Sub
>
>How can I allow my colleage to paste the data into an email before exiting
>the macro?
>
>ideally I would like to unprotect the sheet, copy the data, and then
>re-protect the sheet (with out losing the copied data)
>
>I was hoping I could copy the data to the windows clipboard so I can paste
>it at a later time after the sheet has been re-protected
>
>"Gord Dibben" wrote:
>
>> Always a good idea to post your code.
>>
>> What are you doing with the copied data?
>>
>> Maybe you should paste the copied data before re-protecting the sheet or at
>> least before ending the Sub
>>
>> You can place macro buttons on the QAT.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Wed, 2 Jun 2010 11:39:19 -0700, Travis Patterson <Travis
>> (E-Mail Removed)> wrote:
>>
>> >I have a worksheet where I protect the data from users. I wrote a macro to
>> >unprotect the sheet then copy a selection and then protect the sheet again.
>> >The problem is once the macro protects the sheet it loses the data that was
>> >copied and the highligted copy section disapears. Is there a specialway to
>> >copy the data so that it is not lost when I protect the worksheet? Isthere a
>> >better way to do this?
>> >
>> >Also, I have created several buttons on the worksheet which perform various
>> >macros. Is there any way to put the buttons onto the ribbon or anywhere else
>> >in the workbork besides the sheet itself?
>>
>> .
>>
Similar Threads
