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 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
>
--
----------------------------------------------------------------------------------
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
--
----------------------------------------------------------------------------------
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
