+ Reply to Thread
Results 1 to 7 of 7

Copy and paste range as values to specific sheet/range

  1. #1
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Copy and paste range as values to specific sheet/range

    Hi all,
    I am trying to write some VBA to copy and paste a specific range in the active sheet to another sheet again to a specific range, but as values (as I have formatting rules set up in the destination).

    Range for copy: J4:J34
    Sheet copying from: "U4Brief1"

    Range to paste to B4:B34
    Sheet pasting to: "U4 Tracker"

    I want to assign this to a button for colleagues to click to copy the data.

    Any advice greatly appreciated.

    Thanks

    Luke

  2. #2
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Re: Copy and paste range as values to specific sheet/range

    If it's a specific range like that then you may be best using the 'Record Macro' feature under the developer tab. Then just copy and paste it as needed and you will have a code.

    Then you can see how it works...

    If this unsuitable or the range to copy might change over time then I can give you a code for this...

    Strud

  3. #3
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Re: Copy and paste range as values to specific sheet/range

    Hi Strud,
    Many thanks for your quick reply. I need to replicate the code over ten sheets, so any code that I could tweak the source sheet/range would be very very helpful. This is what I have from the macro, does this look right?

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Re: Copy and paste range as values to specific sheet/range

    Yeah that looks fine. The code can be shortened a bit but if it works it works.

    If by replicate you mean copy to more sheets then all you would have to us add an extra paste bit in. So assuming your third sheet is 'Sheet3' then it would be:

    Please Login or Register  to view this content.
    Hope that helps

    Strud

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,191

    Re: Copy and paste range as values to specific sheet/range

    Please Login or Register  to view this content.
    These two lines will show errors

    Please Login or Register  to view this content.
    Meant to be copy
    I have assumed that the first line
    Range("J4:L34").Select
    refers to active sheet.

  6. #6
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Re: Copy and paste range as values to specific sheet/range

    Hi,
    I am working with this code:
    Please Login or Register  to view this content.
    from Strud. Every time I run it, it works successfully as I want, but shows an error message: "Subscript out of range". What would be causing this?

    Cheers

    Luke


    UPDATE:

    Solved - error with Sheet3 named incorrectly.
    Last edited by lukestkd; 03-04-2014 at 05:07 AM.

  7. #7
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Re: Copy and paste range as values to specific sheet/range

    Hi Luke, AB33's response is correct that was my mistake. Try adjusting it as they suggested and you should see no error message.

    Apologies.

    Strud

+ 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. copy a range of cells N times and paste in separate sheet and also change a specific colum
    By maramkarthik in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2013, 04:27 AM
  2. Copy and paste specific range to date specific range in alternate sheet
    By alanalmarza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 08:29 AM
  3. Pick, copy and paste rows containing specific range of values
    By Kenneyd71 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2012, 09:44 AM
  4. [SOLVED] Copy specific range from one sheet and paste to next available row of different sheet
    By koklok in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-28-2012, 07:27 PM
  5. Copy paste a range of values into a different excel sheet
    By Hugopellegrini in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-21-2012, 05:15 AM

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