Copy & Paste code more efficient

[Desert Piranha]

Hi all,
In an effort to make this code shorter and more efficient i am
attempting to rewrite it.
It copies a range from one workbook and pastes into another workbook.
I have several blocks of code like this.

Any suggestions appreciated.

Original:
'Copy and paste site data
Windows("UCPSITE-06.xls").Activate
Sheets("UCP SITE - Totals").Select
Range("BJ5:BJ617").Select
Selection.Copy
Windows("3140UCP2006WithShell.xls").Activate
Sheets("UCP All Site Rankings").Select
Range("I2").Select
ActiveSheet.Paste
'Clears Clipboard
Application.CutCopyMode = False

New:
'Copy and paste site data
Windows("UCPSITE-06.xls").Activate
Sheets("UCP SITE - Totals").Range("BJ5:BJ617").Copy
Windows("3140UCP2006WithShell.xls").Activate
Sheets("UCP All Site Rankings").Range("I2").Paste
'Clears Clipboard
Application.CutCopyMode = False

 

[Guest]

This is even leaner:

Workbooks("UCPSITE-06.xls").Sheets("UCP SITE -
Totals").Range("BJ5:BJ617").Copy
Destination:=Workbooks("3140UCP2006WithShell.xls").Sheets("UCP All Site
Rankings").Range("I2")

 

[Ron de Bruin]

Try this

Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals").Range("BJ5:BJ617").Copy _
Workbooks("3140UCP2006WithShell.xls").Sheets("UCP All Site Rankings").Range("I2")

 

[Desert Piranha]

Ron & Carlo,

All i can say is WOW.

Workbooks("UCPSITE-06.xls").Sheets("UCP SITE -
Totals").Range("BJ5:BJ617").Copy _
Workbooks("3140UCP2006WithShell.xls").Sheets("UCP All Site
Rankings").Range("I2")

Excellent, Thx very much

 

[Desert Piranha]

Hi,

The code you guys gave works wonderfully, but in another part of the
workbook where i am doing
the same thing i have to "Paste Special" because i just want the values
and not the format & formulas.

So how do i add the Paste Special to the lines you gave me?

Original:
Windows("UCPSITE-06.xls").Activate
' Range("BK227:BK304").Select
' Selection.Copy
' Windows("3140UCP2006WithShell.xls").Activate
' Range("BK3").Select
' Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
' False, Transpose:=False


After:
Workbooks("UCPSITE-06.xls").Sheets("UCP SITE -
Totals").Range("BK227:BK304").Copy _
Workbooks("3140UCP2006WithShell.xls").Sheets("3140 UCP Totals
2006").Range("BK3").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False

 

[Dave Peterson]

Drop the continuation character:

Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals") _
.Range("BK227:BK304").Copy

'new line of code!
Workbooks("3140UCP2006WithShell.xls")
.Sheets("3140 UCP Totals 2006").Range("BK3").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

 

[Guest]

Another thing you might want to look at is the constant re-referencing of
objects, I.e.

Windows("UCPSITE-06.xls").ACTIVATE
' Range("BK227:BK304").SELECT
' Selection.Copy
' Windows("3140UCP2006WithShell.xls").ACTIVATE
' Range("BK3").SELECT

I've noticed this on quite a number of the posts and it obviously comes from
using the macro recorder. However, it really slows down your program and it
hides the object browser. So for example, if you swipe out the ".Select"
after the Range refernce (Range("BK3").Select) and then re-typed in the dot
after the object, you will see the object's properties and methods. That's a
really big bonus in establishing that a) you are using the right object and
or a valid object and b) you can quickly see what methods/properties etc are
availiable to use.

 

[Desert Piranha]

Hi Dave,

Thx this works great.

Drop the continuation character:

Workbooks("UCPSITE-06.xls").Sheets("UCP SITE - Totals") _
Range("BK227:BK304").Copy

'new line of code!
Workbooks("3140UCP2006WithShell.xls")
Sheets("3140 UCP Totals 2006").Range("BK3").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False,
Transpose:=False

Carlos,
Thx, Yeah, its easy to see i'm useing the recorder. i'm trying to do it
on my own,
rather than just asking someone to write it for me. Thx for your help.

Ron,
Thx for your help.

I got a whole pargraph of code to a couple lines.

 

[Dave Peterson]

Give yourself some time to tweak your recorded code.

Once you start doing that tweaking, you'll find that any future updates will be
easier to make. It can be pretty difficult to see what's really happening with
recorded code.

 

[Desert Piranha]

Give yourself some time to tweak your recorded code.

Once you start doing that tweaking, you'll find that any future updates
will be
easier to make. It can be pretty difficult to see what's really
happening with
recorded code.

Dave PetersonHi Dave,

Thx, i can take out a lot of junk, but the compiling or combining
code,
and eliminating "Select", i am trying to learn.

The kind replies to this post has helped me a lot.

Thx Again

 

[Dave Peterson]

You can learn a lot by lurking in the newsgroups, too.

You'll find lots of different ways to approach problems -- some you'll like and
some you won't. <vbg>

 

[NickHK]

As well as all the other good advice:
The macro recorder generates many Windows(xxx) statements, presumably
because it is working graphically.
However, normally it is better (or necessary) to work with the
Workbooks(xxx) object instead.
Unless you are changing the appearance of the window (position, zoom, panes,
etc) work with a workbook object.

NickHK

"Desert Piranha"

 

[Desert Piranha]

As well as all the other good advice:
The macro recorder generates many Windows(xxx) statements, presumably
because it is working graphically.
However, normally it is better (or necessary) to work with the
Workbooks(xxx) object instead.
Unless you are changing the appearance of the window (position, zoom,
panes,
etc) work with a workbook object.

NickHKHi Nick,

You know i noticed the "Windows("UCPSITE-06.xls").Activate" etc, but i
didn't know what it meant.

Thx very much for this.