Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Migrating from Copy - Destination to Paste Values

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Migrating from Copy - Destination to Paste Values

    I'm new to VBA. I have a simple snippet, it uses Copy Destination...however I need to paste VALUES. I believe I can't use destination in order to do this, however all of my attempts have caused issues. Any thoughts on a simple fix? Thanks!



    Code:
    Sub MoveSC()
        Dim i As Variant
        Dim endrow As Integer
        Dim CL As Worksheet, SC As Worksheet
        
        'clears contents of scorecard
        Worksheets("Scorecard").Activate
        ActiveSheet.Range("A1:AA50").ClearContents
    
    
        Set CL = ActiveWorkbook.Sheets("Control")
        Set SC = ActiveWorkbook.Sheets("Scorecard")
    
    
        endrow = CL.Range("A" & CL.Rows.count).End(xlUp).row
    
    
        For i = 2 To endrow
            If CL.Cells(i, "M").Value = "1" Then
               CL.Cells(i, "M").EntireRow.Copy Destination:=SC.Range("A" & SC.Rows.count).End(xlUp).Offset(1)
            End If
        Next
    End Sub
    Last edited by Jerry Sullivan; Apr 21st, 2017 at 04:23 PM. Reason: added code tags

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,486
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Migrating from Copy - Destination to Paste Values

    Welcome to MrExcel,

    To do that, you would replace that statement with two statements:

    Code:
       CL.Cells(i, "M").EntireRow.Copy 
       SC.Range("A" & SC.Rows.count).End(xlUp).Offset(1).PasteSpecial(xlPasteValues)
    Using Excel 2016

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com