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

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

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

--

Dave Peterson

Dave Peterson Wrote:
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


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

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

Desert Piranha wrote:

Dave Peterson Wrote:
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

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

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.2clv16_1155685510.3865@excelforu m-nospam.com wrote in
message news:Desert.Piranha.2clv16_1155685510.3865@excelfo rum-nospam.com...

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

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


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