+ Reply to Thread
Results 1 to 8 of 8

Copy dynamic range from previous sheet and paste after last used row in a range

  1. #1
    Registered User
    Join Date
    11-20-2009
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    25

    Copy dynamic range from previous sheet and paste after last used row in a range

    Hello All,

    I am trying to copy the values from a dynamic range ("C7:E66")on the previous sheet onto the current active sheet, but I need to paste the values after the last used row in a range ("C7:E66")

    I can get it to copy the data on the previous sheet no problem, however I am stuck as it is not pasting after the last used rown on the active sheet that the macro is run from.

    Here is what I have so far

    I appreciate any help offered greatly as I'm pulling my hair out now!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    Do not use relative references to sheets in VBA.

    Please Login or Register  to view this content.
    Last edited by snb; 08-05-2010 at 04:26 AM.

  3. #3
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    Quote Originally Posted by Mistweaver View Post
    Hello All,

    I am trying to copy the values from a dynamic range ("C7:E66")on the previous sheet onto the current active sheet, but I need to paste the values after the last used row in a range ("C7:E66")

    I can get it to copy the data on the previous sheet no problem, however I am stuck as it is not pasting after the last used rown on the active sheet that the macro is run from.

    Here is what I have so far

    I appreciate any help offered greatly as I'm pulling my hair out now!

    Please Login or Register  to view this content.
    Hi,

    Not sure if this is what your looking for but maybe try:

    Please Login or Register  to view this content.
    Thank You, Mike

  4. #4
    Registered User
    Join Date
    11-20-2009
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    Quote Originally Posted by snb View Post
    Do not use relativer references to sheets in VBA.

    Please Login or Register  to view this content.

    Thanks snb, but the reason I am using relative references is that the number of sheets will change weekly and so I cannot use the index number.

    Mike,

    Thanks for helping, but this does not work. It pastes much further down the sheet instead of after the last used row in range C7:E66 which is where I need to paste the data.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    Surely this will return the same Ro
    Please Login or Register  to view this content.
    Try attaching a workbook
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    The last added sheet is always sheets(1)
    the oldest (first) sheet is sheets(sheets.count))
    than this will do

    Please Login or Register  to view this content.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    Quote Originally Posted by snb View Post
    The last added sheet is always sheets(1)
    the oldest (first) sheet is sheets(sheets.count))
    That is not true. The sheet on the left of the tab order is sheets(1), the sheet on the right is sheets(sheets.count). The age or timing is irrelevant.
    Remember what the dormouse said
    Feed your head

  8. #8
    Registered User
    Join Date
    11-20-2009
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Copy dynamic range from previous sheet and paste after last used row in a range

    I still haven't had any luck with this so any more help would be greatly appreciated.

    I have attached the workbook.

    Basically I need to copy the figures from the sheet before to the sheet next to it after the last row of data.
    Attached Files Attached Files

+ 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