+ Reply to Thread
Results 1 to 7 of 7

More efficient copy/paste??

  1. #1
    Forum Contributor
    Join Date
    02-03-2005
    Location
    Chicago, IL
    Posts
    101

    Question More efficient copy/paste??

    TIA!!!

    Is there a more efficient way to perform this copy paste? I know you should always avoid selecting if possible. I have tried a number of things and had no success. Here is the snippet of code I want to streamline:

    If rngNHO Is Nothing Then GoTo skipNHO
    Sheets("ALL").Select
    rngNHO.Copy
    Sheets("Contribs").Select
    Cells(Range("A3").End(xlDown).Row + 1, "A").Select
    ActiveSheet.Paste
    skipNHO:

    I have several snippets like this in my macro. Any help is greatly appreciated!!

  2. #2
    Ardus Petus
    Guest

    Re: More efficient copy/paste??

    If Not rngNHO Is Nothing Then
    rngNHO.Copy _
    Destination:=Worksheets("Contribs") _
    .Range("A3").End(xlDown).Offset(1, 0)
    End If

    HTH
    --
    AP


    "Celt" <[email protected]> a écrit dans le
    message de news: [email protected]...
    >
    > TIA!!!
    >
    > Is there a more efficient way to perform this copy paste? I know you
    > should always avoid selecting if possible. I have tried a number of
    > things and had no success. Here is the snippet of code I want to
    > streamline:
    >
    > If rngNHO Is Nothing Then GoTo skipNHO
    > Sheets("ALL").Select
    > rngNHO.Copy
    > Sheets("Contribs").Select
    > Cells(Range("A3").End(xlDown).Row + 1, "A").Select
    > ActiveSheet.Paste
    > skipNHO:
    >
    > I have several snippets like this in my macro. Any help is greatly
    > appreciated!!
    >
    >
    > --
    > Celt
    > ------------------------------------------------------------------------
    > Celt's Profile:
    > http://www.excelforum.com/member.php...o&userid=19413
    > View this thread: http://www.excelforum.com/showthread...hreadid=550393
    >




  3. #3
    Die_Another_Day
    Guest

    Re: More efficient copy/paste??

    Set rngNHO = Sheets("All").Range("A1")
    Cells(Range("A3").End(xlDown).Row + 1, "A").Select
    rngNHO.Copy ActiveCell

    This assumes that you are on the Sheet that you want to paste the data
    into.

    HTH

    Die_Another_Day


  4. #4
    Forum Contributor
    Join Date
    02-03-2005
    Location
    Chicago, IL
    Posts
    101
    Thanks guys!

    Quick question Ardus...

    With your code, if rngNHO is nothing...will that cause an error?

  5. #5
    Die_Another_Day
    Guest

    Re: More efficient copy/paste??

    if you don't set the rngNHO to some range then it is empty...

    Die_Another_Day

    Celt wrote:
    > Thanks guys!
    >
    > Quick question Ardus...
    >
    > With your code, if rngNHO is nothing...will that cause an error?
    >
    >
    > --
    > Celt
    > ------------------------------------------------------------------------
    > Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
    > View this thread: http://www.excelforum.com/showthread...hreadid=550393



  6. #6
    Ardus Petus
    Guest

    Re: More efficient copy/paste??

    No: it will do nothing.

    --
    AP

    "Celt" <[email protected]> a écrit dans le
    message de news: [email protected]...
    >
    > Thanks guys!
    >
    > Quick question Ardus...
    >
    > With your code, if rngNHO is nothing...will that cause an error?
    >
    >
    > --
    > Celt
    > ------------------------------------------------------------------------
    > Celt's Profile:
    > http://www.excelforum.com/member.php...o&userid=19413
    > View this thread: http://www.excelforum.com/showthread...hreadid=550393
    >




  7. #7
    Die_Another_Day
    Guest

    Re: More efficient copy/paste??

    for Ardus' code it should not because of the if not rngNHO = Nothing

    HTH

    Die_Another_Day
    Celt wrote:
    > Thanks guys!
    >
    > Quick question Ardus...
    >
    > With your code, if rngNHO is nothing...will that cause an error?
    >
    >
    > --
    > Celt
    > ------------------------------------------------------------------------
    > Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
    > View this thread: http://www.excelforum.com/showthread...hreadid=550393



+ 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