Used Range copy Paste Values
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Used Range copy Paste Values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Used Range copy Paste Values

    I'm trying to do something that should be fairly simple, but I'm having trouble. What I want is to copy the Used Range from my View1 sheet and paste only the values into my View1Pivot sheet. Here's the code I'm trying to use:
    Code:
    'Copy the View1 Information
        UPDT.Sheets("View1").UsedRange.Value.Copy Destination:=Tool.Sheets("View1Pivot").Cells(1, 1)
    If I take out "Value" then the code works just fine at copying and pasting, but how do I change it to only give me the values?
    Thanks,
    John

  2. #2
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Used Range copy Paste Values

    Try

    Code:
    UPDT.Sheets("View1").UsedRange.Copy
    Tool.Sheets("View1Pivot").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Dec 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Used Range copy Paste Values

    VoG,

    That one works just fine. I guess there's not a way to do it as a one-liner, using the "Copy Destination:" routine?

  4. #4
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Used Range copy Paste Values

    No, it has to go on two lines.
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Dec 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Used Range copy Paste Values

    Thanks Peter. I appreciate the help.

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,679
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Used Range copy Paste Values

    You can assign the value of one to the other as long as you size both ranges the same.

  7. #7
    New Member
    Join Date
    Dec 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Used Range copy Paste Values

    How do you do that? One range (copied from) has a size, the range of the "UsedRange", but the other is a blank sheet. How do you "size" that range?

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,679
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Used Range copy Paste Values

    Like so:
    Code:
    With UPDT.Sheets("View1").UsedRange
       Tool.Sheets("View1Pivot").Cells(1, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    Re your reported post, we don't mark threads solved here - this isn't ExcelForum. (it's working for a start. )

  9. #9
    New Member
    Join Date
    Dec 2009
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Used Range copy Paste Values

    Thanks Rory,

    Would the resizing type routine run faster than the copy/paste routine? It seems like it would, because it bypasses the clipboard. I'm stringing a lot of operations together, so the faster routines are very much appreciated.

    Thanks for letting me know about not marking the posts 'Solved'. I'll try to remember that next time I'm here.

    Thanks,
    John

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,679
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Used Range copy Paste Values

    I would suspect so, yes. Value2 would be even quicker.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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