+ Reply to Thread
Results 1 to 13 of 13

Copy & Paste code more efficient

  1. #1
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256

    Copy & Paste code more efficient

    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
    'The sheet i want to copy from has to be active
    Range("BJ5:BJ617").Select
    Selection.Copy
    Windows("3140UCP2006WithShell.xls").Activate
    'The sheet i want to paste to has to be active
    Range("I2").Select
    ActiveSheet.Paste
    'Clears Clipboard
    Application.CutCopyMode = False

    New:
    'Copy and paste site data
    Windows("UCPSITE-06.xls").Activate
    'in case the sheet is not active call it
    Sheets("UCP SITE - Totals").Range("BJ5:BJ617").Copy
    Windows("3140UCP2006WithShell.xls").Activate
    'in case the sheet is not active call it
    Sheets("UCP All Site Rankings").Range("I2").Paste
    'Clears Clipboard
    Application.CutCopyMode = False
    Last edited by Desert Piranha; 08-15-2006 at 04:47 PM.
    Thx
    Dave
    "The game is afoot Watson"

  2. #2
    CarloC
    Guest

    RE: Copy & Paste code more efficient

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


  3. #3
    Ron de Bruin
    Guest

    Re: Copy & Paste code more efficient

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >




  4. #4
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256
    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

  5. #5
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256

    Adding Paste Special

    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

  6. #6
    Dave Peterson
    Guest

    Re: Copy & Paste code more efficient

    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

  7. #7
    CarloC
    Guest

    Re: Copy & Paste code more efficient

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


  8. #8
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256
    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.

  9. #9
    Dave Peterson
    Guest

    Re: Copy & Paste code more efficient

    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

  10. #10
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256
    Quote Originally Posted by 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.

    Dave Peterson
    Hi 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

  11. #11
    Dave Peterson
    Guest

    Re: Copy & Paste code more efficient

    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

  12. #12
    NickHK
    Guest

    Re: Copy & Paste code more efficient

    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"
    <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  13. #13
    Forum Contributor
    Join Date
    11-20-2005
    Posts
    256
    Quote Originally Posted by 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
    Hi 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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1