Results 1 to 3 of 3

Thread: Copy cells from Sheet2, paste to next blank row in Sheet1

  1. #1
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13

    Copy cells from Sheet2, paste to next blank row in Sheet1

    Hi.
    the code below, demand data "Sheet1" with discretion in "Sheet2"
    to find it overrides the value of "Sheet2" and put in "Sheet1"
    Code:
    Option Explicit
    
    Sub Macro1()
    
        Dim rngCell As Range
        Dim lngMatchRowNum As Long
        Application.ScreenUpdating = False
        With Sheets("Sheet1")
            For Each rngCell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
                If (IsError(Evaluate("VLOOKUP(" & Sheets("Sheet1").Range(rngCell.Address(False, False)) & ",Sheet2!A:A,1,FALSE)"))) = False Then
                    lngMatchRowNum = Evaluate("MATCH(" & Sheets("Sheet1").Range(rngCell.Address(False, False)) & ",Sheet2!A:A,0)")
                    .Range("B" & rngCell.Row & ":F" & rngCell.Row).Value = Sheets("Sheet2").Range("B" & lngMatchRowNum & ":F" & lngMatchRowNum).Value
                End If
            Next rngCell
        End With
        Application.ScreenUpdating = True
        MsgBox "Todos os registros aplicáveis já foram atualizados."
     
    End Sub
    Now what I need is:
    If the value of "Sheet2" not found in "Sheet2" so that the value
    the "Sheet2" is inderido the next empty line (below) of "Sheet1"
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try

    Code:
    Option Explicit
    
    Sub kTest()
        
        Dim x, K1 As Range, K2, i As Long, r As Long
        
        Set K1 = Sheet1.Range("a1").CurrentRegion.Columns(1)
        K2 = Sheet2.Range("a1").CurrentRegion.Value2
        
        r = K1.Rows.Count
        For i = 2 To UBound(K2, 1)
            x = Application.Match(K2(i, 1), K1.Value2, 0)
            If IsError(x) Then
                K1.Cells(1).Offset(r).Resize(, UBound(K2, 2)) = Application.Index(K2, i, 0)
                r = r + 1
            End If
        Next
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    13
    Hi.

    Was grerat!!

    Thank you!!

Similar Threads

  1. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  2. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  3. VBA -- Copy/Paste across sheets
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 09-21-2012, 02:07 PM
  4. Highlighting Blank Cells
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 08-13-2012, 07:56 AM
  5. Trapping Copy To Range Before Copy/Cut Paste
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-07-2011, 07:48 PM

Tags for this Thread

Posting Permissions

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