How to avoid pasting duplicate Range from one worksheet to another

Question

I want to copy data from a worksheet named "copySheet" to the first blank row in a sheet named "pasteSheet".

If the data in cell A2 of copySheet is in first column of pasteSheet then provide an error message "data is already existed and avoid pasting" otherwise paste the copy range from copySheet to pasteSheet.

I have written code as below however, IF loop is not working correctly. The value in A2 cell found in first column of pasteSheet but code is ignoring the loop and pastes the range again.

Sub Macro1()
'
' Macro1 Macro
'
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")

copySheet.Columns("A:D").Select
Selection.ClearContents

ActiveSheet.Paste Destination:=copySheet.Range("A1")

Dim FoundRange As Range
Dim Search As String
Search = copySheet.Cells(2, 1).Select
Set FoundRange = pasteSheet.Columns(0, 1).Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
If Foundcell Is Nothing Then
    Dim N As Long
    N = copySheet.Cells(1, 1).End(xlDown).Row
    Range("A2:E" & N).Select
    Selection.Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Else
    MsgBox "Data Exists" & " data found at cell address " & Foundcell.Address
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

`pasteSheet.Columns(0, 1)` is not right. Where should it be searching? — SJR, Feb 03 '20 at 17:44

score 1 · Accepted Answer · answered Feb 03 '20 at 17:49

1

Try this. A few problems with your code:

as noted above, your Columns syntax was off
you defined FoundRange but then referred to FoundCell - use Option Explicit to flag up these errors
avoid Select wherever possible

    Option Explicit

    Sub Macro1()

    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet

    Set copySheet = Worksheets("Sheet1")
    Set pasteSheet = Worksheets("Sheet2")

    With copySheet
        .Columns("A:D").ClearContents
        Dim FoundRange As Range
        Dim Search As String
        Search = .Cells(2, 1)
        Set FoundRange = pasteSheet.Columns(1).Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
        If FoundRange Is Nothing Then
            Dim N As Long
            N = .Cells(Rows.Count, 1).End(xlUp).Row + 1
            .Range("A2:E" & N).Copy
            pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Else
            MsgBox "Data Exists" & " data found at cell address " & FoundRange.Address
        End If
    End With

    End Sub

answered Feb 03 '20 at 17:49

SJR

22,986
6
18
26

thanks for correcting my mistake. It is really helpful. However, I'm getting a different error now. I already have copied contents that will paste in copySheet after clearing the contents from column A:D and then use Search variable. While I'm executing code it says "Paste Special Method" or "Range class failed". Any suggestion to get rid of this error? – Dhruv Bhatt Feb 04 '20 at 14:22
I noticed your original code had a paste line but no copy line. You need to copy before you paste. – SJR Feb 04 '20 at 14:27
Yes, that is correct. I am copying data from the Tableau as Cross Tab. After copying from Tableau, I'm clearing contents in copySheet and then paste it over there. I ended up adding a dummy sheet in Excel to paste the contents. However, If you have any better idea let me know. – Dhruv Bhatt Feb 04 '20 at 14:43
OK I think the Tableau aspect is probably a separate question. But if the paste works you can add it back in above. I'd have thought there have been a better way to connect Tableau and Excel and I don't know much about the former. – SJR Feb 04 '20 at 16:02
Re the error in your first comment, which line errors? – SJR Feb 04 '20 at 16:06
After the code .Columns("A:D").ClearContents, I've added the another line for pasting the data. Which was .Range("A1").PasteSpecial Paste:=xlPasteValues And in this line I'm getting error for Paste Special method. – Dhruv Bhatt Feb 04 '20 at 16:42
Maybe PS doesn't work with external sources. Try looking into the clipboard. – SJR Feb 04 '20 at 17:08
Sure. Thanks. However, in my initial code where I was using ActiveSheet.Paste Destination:=copySheet.Range("A1"), I was able to paste data from external source perfectly fine. – Dhruv Bhatt Feb 04 '20 at 17:14
Oh ok well why don't you use that then? – SJR Feb 04 '20 at 17:19
1

Yes used that line and it worked perfectly fine. Again, thank you for your guidance and will try to avoid using Select statement if possible in VBA code. – Dhruv Bhatt Feb 04 '20 at 18:02

How to avoid pasting duplicate Range from one worksheet to another

1 Answers1