Results 1 to 2 of 2

Migrating from Copy - Destination to Paste Values

This is a discussion on Migrating from Copy - Destination to Paste Values within the Excel Questions forums, part of the Question Forums category; I'm new to VBA . I have a simple snippet, it uses Copy Destination...however I need to paste VALUES. I ...

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    1

    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,443

    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

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
  •  


DMCA.com