Copying Data without overwriting (edit macro)

I have the following macro that copies data from sheet1 and pastes it to sheet2.

I want to edit this macro to not overwrite the data in sheet2 when pasting in the data from sheet1. it should only paste in the data if the respective cell is empty. if something is already there it should just move on to the next cell.

Previously what this macro did was it saved the old data as a comment in the respective cell (sheet2) and pasted in the data from sheet1.

JN

Option Explicit
Option Base 1

Sub CopyData()
Dim MyRg As Range
Dim MyData()
Dim SrchRg As Range
Dim MyVal As Range
Dim F
Dim ColSRC, ColDEST
Dim I As Integer
Dim TextVal As String
Dim NbCol As Integer
    NbCol = 3           '  NB OF COLUMNS TO TREAT
    ReDim MyData(NbCol)
    ColSRC = Array("D", "F", "H")  '  HERE  TO MENTION COLUMNS IN SHEET 1  WHERE TO GET DATA
    ColDEST = Array("F", "D", "I") '  HERE  TO MENTION COLUMNS IN SHEET 2  WHERE TO PÜT DATA
    
    With Sheets("Sheet1")
        Set MyRg = .Range(.Range("C5"), .Range("C" & Rows.Count).End(xlUp))
        On Error Resume Next
        For Each MyVal In MyRg
            For I = 1 To NbCol
                MyData(I) = .Cells(MyVal.Row, ColSRC(I))
            Next I
            
            With Sheets("Sheet2")
                Set SrchRg = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
                With SrchRg
                    Set F = .Find(What:=MyVal, After:=.Cells(1, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False)
                    If (Not F Is Nothing) Then
                        For I = 1 To NbCol
                            With .Cells(F.Row, ColDEST(I))
                                If (.Value <> Empty) Then
                                    .AddComment
                                    .Comment.Visible = False
                                    TextVal = .Value
                                    .Comment.Text Text:=TextVal
                                End If
                                .Value = MyData(I)
                            End With
                        Next I
                    End If
                End With
            End With
        Next MyVal
    End With
End Sub

posting here too:
http://www.mrexcel.com/forum/showthread.php?t=532593

Let me explain a bit detail how the macro works.
- We compare every value in sheet1 column C with sheet2 coloumn A to find a match

If there is a match then do this in that respective row of the matching value in sheet2:
- copy the data from the respective columns in sheet1 (D,F,H) and paste it in the sheet2 columns F, D, I respectively.

Now what I want is that when the value are to be pasted in the columns of sheet2 they should not overwrite the already entered values in the respective cell. it should just move on.

I hope this clears your question.

it's always better to have sample of the workbook, though you can test it by yourself:

Option Explicit
Option Base 1

Sub CopyData()
Dim MyRg As Range
Dim MyData()
Dim SrchRg As Range
Dim MyVal As Range
Dim F
Dim ColSRC, ColDEST
Dim I As Integer
Dim TextVal As String
Dim NbCol As Integer
    NbCol = 3           '  NB OF COLUMNS TO TREAT
    ReDim MyData(NbCol)
    ColSRC = Array("D", "F", "H")  '  HERE  TO MENTION COLUMNS IN SHEET 1  WHERE TO GET DATA
    ColDEST = Array("F", "D", "I") '  HERE  TO MENTION COLUMNS IN SHEET 2  WHERE TO PЬT DATA
    
    With Sheets("Sheet1")
        Set MyRg = .Range(.Range("C5"), .Range("C" & Rows.Count).End(xlUp))
        On Error Resume Next
        For Each MyVal In MyRg
            For I = 1 To NbCol
                MyData(I) = .Cells(MyVal.Row, ColSRC(I))
            Next I
            
            With Sheets("Sheet2")
                Set SrchRg = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
                With SrchRg
                    Set F = .Find(What:=MyVal, After:=.Cells(1, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False)
                    If (Not F Is Nothing) Then
                        Do
                            If .Cells(F.Row, "f") = "" And .Cells(F.Row, "d") = "" And .Cells(F.Row, "i") = "" Then
                                .Cells(F.Row, "f").Value = MyVal.Offset(, 1).Value
                                .Cells(F.Row, "d") = MyVal.Offset(, 3).Value
                                .Cells(F.Row, "i") = MyVal.Offset(, 5).Value
                                Exit Do
                            Else
                                F.Row = F.Row + 1
                            End If
                        Loop
                    End If
                End With
            End With
        Next MyVal
    End With
End Sub

Yes for this case the macro works.
what changes do i need to make (apart from these on the top of the code NbCol , ColSRC , ColDEST ) to copy the data between the following 16 columns from sheet 1 to sheet 2

Sheet1 : C,A, B, N, M,E,H,F,G,J,K,L, I, Q, D, O
Sheet2: D,O, Q, V, AF, AG, AH, AM, AN, AR, AS, AT, AY, BA, BK, BL

And also we are now comparing all the values in Sheet1 Column P (row 2 and onwards) with Sheet2 Column A (row 5 and onwards) to find a match before copying the data over.


Can you please edit your code to accommodate these two changes .

ok, I'll have a look. As far as I see it's a completely separate question, right?

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Note: this means posting links to ALL your cross-posts.

I said ALL your cross posts. You did not link to this one at OzGrid, for example. (there may be others for all I know)