Prev: Updateing Access DB
Next: Summing Range
From: Travis Patterson on 3 Jun 2010 13:44
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-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?
> >>
> >> .
> >>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Updateing Access DB
Next: Summing Range