Results 1 to 3 of 3

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Member
    Join Date
    Aug 2012
    Posts
    72
    Rep Power
    12

    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

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
  •