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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

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")
--
3c


"Desert Piranha" wrote:


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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

Try this

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Desert Piranha" <Desert.Piranha.2clm8j_1155674113.3466@excelforu m-nospam.com wrote in message
news:Desert.Piranha.2clm8j_1155674113.3466@excelfo rum-nospam.com...

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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

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


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

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

Desert Piranha wrote:

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

--
Desert Piranha

------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

--

Dave Peterson

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.

--
3c


"Desert Piranha" wrote:


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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946

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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=571946