Copy & Paste code more efficient

D

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
 
C

CarloC

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")
 
R

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")
 
D

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
 
D

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
 
D

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
 
C

CarloC

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

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

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

Desert Piranha

Dave said:
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
 
D

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

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

Desert Piranha

NickHK said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top