Thanks PaulS - DDE solved the problem.
On Apr 15, 12:30 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> As Dave suggested, using a copy/paste in a macro is very inefficient.
>
> using:
> Sheets("Schema").Select
> Range("A1:Z1000").Select
> Range("Z1000").Activate
> Selection.Copy
> Sheets("NewSheet").Select
> Range("A1").Select
> ActiveSheet.Paste
>
> will work, but it must copy from the sheet to the clipboard, then copy from
> the
> clipboard to the new sheet.
>
> If you're only copying VALUES:
>
> Sheets("NewSheet").Select
> Sheets("NewSheet").Range("A1").Value =
> Sheets("Schema").Range("A1:Z1000").Value
>
> will work almost instantaneously!
>
> Now, if you're really stuck on using the copy/paste scenario (because you want
> to copy formats/colors/fonts/etc)
>
> then another option would be to open in a separate instance.
> The second instance has it's own clipboard.
>
> Depending on the version of excel you're using, there's different techniques
> for
> doing this.
>
> excel 2007 has the option of using DDE (Dynamic Data Exchange)
> This is how you "normally" operate.
> It allows you to "exchange" data with other applications.
> If you turn it off, then if you copy, then select another workbook, you cannot
> paste it.
> Which is what you're looking for here.
>
> In other versions of excel, I think there was an option under
> Tools->Options->General
> called "ignore other applications"
>
> Basically, you're telling Excel to run "stand-alone"..
>
> hope this helps.
>
> PaulS
>
>
>
> ________________________________
> From: Dave Bonallack <davebonall...@hotmail.com>
> To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
> Sent: Thu, April 14, 2011 9:56:24 AM
> Subject: Re: $$Excel-Macros$$ "Ring-Fence" macro's copy/paste, so can
> copy/paste
> without interference outside the macro
>
> Hi Tom,
> Problem is, when you use copy/paste in a macro, it uses the same clip board as
> any other application that's running. The solution is probably to eliminate
> copy/paste from your macro. It's a very inefficient way to run, and there's
> nearly always a better and much quicker way to do the same thing.
> Regards - Dave
>
> On 14/04/2011, at 10:37 AM, "tomfabtas...@hotmail.com"
>
>
>
>
>
> <tomfabtas...@hotmail.com> wrote:
> > Hi,
> > I run an excel macro on a loop (Excel 2007), which runs for most of
> > the day and includes copy/paste code. While this macro is running, in
> > another instance of excel I need to copy and paste (manually, not
> > using a macro). Sometimes when I manually copy/paste, I get the paste
> > from the macro. So, the macro is doing a copy just after I manually do
> > a copy.
> > Is there anyway to "ring-fence" the macro, so that the copy/paste does
> > not interfer with the other work I am doing ?
> > Regards,
> > Tom
>
> > --
> >----------------------------------------------------------------------------------
> >-
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> ----------------------------------------------------------------------------------
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
