VB - Need to optimise/alternative to copy and paste

**Andrew-R** · 10-13-2011, 03:34 AM

The first thing to look at would be the number of .Select and .Activate commands you have in your code.

In VBA it's hardly ever necessary to activate or select objects before you use them, so

ActiveCell.Offset(0, 4).Select
Set MyRange = Range(ActiveCell, ActiveCell.Offset(0, 2))
MyRange.Copy
Range("I3").Activate
ActiveCell.Offset(Count2, (Count * 3)).Activate
ActiveCell.PasteSpecial (xlPasteValues)

Can be cut down to

Range(Range("I3").Offset(Count2,Count*3),Range("I3").Offset(Count2,Count*3+2)).Value=Range(ActiveCell,.Offset(0,4),ActiveCell.Offset(0,6)).Value

**scaifie** · 10-13-2011, 06:02 PM

Ahhh,

That is a much better way of doing things, I didn't realize how slow the activating and selecting was.

That decreased it from 20 minutes to 1 minute.

There was an extra comma in there so the code is :

Range(Range("I3").Offset(Count2,Count*3),Range("I3").Offset(Count2,Count*3+2)).Value=Range(ActiveCell.Offset(0,4),ActiveCell.Offset(0,6)).Value

I have to admit, i had to go through that line about 10 times to trace exactly where it was going!!

I have changed the code in other areas to not use so much select and activate.

I will attach the files next time, sorry if it was an inconvenience.

Thanks heaps Andrew for your help, I really appreciate it.

VB - Need to optimise/alternative to copy and paste

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: VB - Need to optimise/alternative to copy and paste

Re: VB - Need to optimise/alternative to copy and paste

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions