+ Reply to Thread
Results 1 to 6 of 6

Making Copy and Paste with .end offset more efficient

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Making Copy and Paste with .end offset more efficient

    Hello again everyone. I am hoping there is a simple solution to this. I want to convert this code to a single line if at all possible. I have several variations on this in the same macro (different sources and destinations) I am wondering how I would be able to combine these lines to something similar to the copy statement followed by the destination on the same line.

    This is the code I am trying to combine as I said I have several blocks like this. It works fine but I am trying to reduce lines.

    Please Login or Register  to view this content.
    Thanks again.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,245

    Re: Making Copy and Paste with .end offset more efficient

    Try this:

    Please Login or Register  to view this content.
    Basically, eliminate the Selects. They are not needed.

  3. #3
    Registered User
    Join Date
    08-19-2014
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    2010
    Posts
    45

    Re: Making Copy and Paste with .end offset more efficient

    Try:
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,826

    Re: Making Copy and Paste with .end offset more efficient

    Perhaps this...
    Please Login or Register  to view this content.
    It would be easier to read as this, though:
    Please Login or Register  to view this content.
    Either allows you to not have to select/activate the sheets. To do so, you have to specify where the range references occur by prefacing them with the sheet name. Jamie's code doesn't do that, so it only works if the Shelf Life Tracking sheet is active.

    I suppose, technically, you should preface the "Rows.Count" reference, but I believe that returns the same value for any sheet (unless maybe the sheet is filtered).
    Last edited by Paul; 08-22-2014 at 03:38 PM.

  5. #5
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Making Copy and Paste with .end offset more efficient

    That is right along the lines of what I was coming up with> Looks like I had the syntax wrong on the first two occurrences of Range. Thank you

  6. #6
    Registered User
    Join Date
    10-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Making Copy and Paste with .end offset more efficient

    Paul, Excellent point. I checked it out and the Rows.Count reference seems to work fine without the preface even with filters that are being turned on before this. I did end up going with the With/ End with statement for its readability. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Most efficient way to copy multiple variables to specific cell locations using offset
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2014, 07:18 AM
  2. Making Copy and Paste Code more efficient
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2006, 11:31 AM
  3. Copy & Paste code more efficient
    By Desert Piranha in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-15-2006, 11:51 PM
  4. More efficient copy/paste??
    By Celt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-09-2006, 02:25 PM
  5. [SOLVED] Efficient Copy/Paste
    By William Benson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2005, 03:05 PM

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