Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data using macro (with worksheet protect)
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data using macro (with worksheet protect)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data using macro (with worksheet protect)
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 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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data using macro (with worksheet protect)
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-clickpaste or editpaste. 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 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 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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data using macro (with worksheet protect)
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) "Gord Dibben" wrote: 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-clickpaste or editpaste. 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 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 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? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro to copy data to a designated worksheet | Excel Discussion (Misc queries) | |||
macro to find data and filter it and copy to another worksheet | Excel Programming | |||
protect worksheet but allow copy column | Excel Worksheet Functions | |||
Protect Worksheet from Copy Past | Excel Discussion (Misc queries) | |||
How to create a macro to copy data from a column to a row in another worksheet? | Excel Programming |