Copy data using macro (with worksheet protect)
in [Programming]
|
Prev: Updateing Access DB
Next: Summing Range
From: Travis Patterson Travis on 2 Jun 2010 14:39 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?
From: Ron de Bruin on 2 Jun 2010 15:06 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm
From: Gord Dibben on 2 Jun 2010 15:14 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 Patterson(a)discussions.microsoft.com> 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 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?
From: Travis Patterson on 2 Jun 2010 20:17 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" 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 > Patterson(a)discussions.microsoft.com> 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 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? > > . >
From: Gord Dibben on 2 Jun 2010 23:10
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 <TravisPatterson(a)discussions.microsoft.com> wrote: >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" 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 >> Patterson(a)discussions.microsoft.com> 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 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? >> >> . >> |