Copy data using macro (with worksheet protect)

[Travis Patterson]

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 special way to
copy the data so that it is not lost when I protect the worksheet? Is there 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?

 

[Ron de Bruin]

We not see your code but try to use this line after the Paste and before you protect the sheet
Application.CutCopyMode = False

For the Ribbon see
http://www.rondebruin.nl/ribbon.htm

If you want to use your code in all workbooks this is a very easy way
http://www.rondebruin.nl/qat.htm

 

[Gord Dibben]

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

 

[Travis Patterson]

Thanks Gord,

After running the "copy data macro" My colleage will paste the data into an
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]

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

 

[Travis Patterson]

Thanks again Gord,

It works great now

and hanks for showing me the code to select a current region.

(I was just using the record macro function because I am new to this)