+ Reply to Thread
Results 1 to 2 of 2

Not copying duplicates

Hybrid View

  1. #1
    Registered User
    Join Date
    08-13-2018
    Location
    taylor mill, kentucky
    MS-Off Ver
    2010
    Posts
    61

    Not copying duplicates

    Hey guys,
    So I have this code here written that takes the selected area and copies it for me based off the cells that I want the values from. How do I check to see if the values that are being copied already exist to where they are being pasted to.
    As an example lets say its coping John (Cell F), and 29 (Cell Q)
    Its going to post at the first empty cell of text from B39 from top down. How can I get it to check above the first empty cell to see if John is already listed and 29 is already listed?
    Have I taken the wrong approach on this?

    Sub Completed()
    
    Dim datasheet As Worksheet 
    Dim reportsheet As Worksheet
    Dim finalrow As Integer
    Dim i As Integer 'row counter
    
    
    
    Set datasheet = Sheet1
    Set reportsheet = Sheet1
    
    datasheet.Select
    finalrow = Range("E" & Rows.Count).End(xlUp).Row
    
    For i = 2 To finalrow
        If Cells(i, 17) <> "" And Cells(i, 17) <> "Completed Date" Then
        Application.Union(Cells(i, "F"), Cells(i, "Q")).Copy
        reportsheet.Select
        Range("B39").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        datasheet.Select
        End If
    Next i
    
    
    reportsheet.Select
    Range("B2").Select
    
    End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,651

    Re: Not copying duplicates

    Check if there are such values in columns B and C. For instance with COUNTIFS function (of course called within VBA):

    For i = 2 To finalrow
        If Cells(i, 17) <> "" And Cells(i, 17) <> "Completed Date" Then
        Application.Union(Cells(i, "F"), Cells(i, "Q")).Copy
        reportsheet.Select
        If WorksheetFunction.CountIfs(Range("B:B"), Cells(i, "F"), Range("C:C"), Cells(i, "Q")) = 0 Then
          Range("B39").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        End If
        datasheet.Select
        End If
    Next i
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA macro for filtering and copying duplicates
    By bizzarofen in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 04-04-2017, 08:59 AM
  2. Code for copying duplicates to other sheet
    By nikhilbansal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2016, 04:49 PM
  3. Code for copying duplicates to other sheet
    By nikhilbansal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2016, 03:53 PM
  4. Removing Duplicates by copying some text
    By nccool in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2013, 06:22 AM
  5. Copying Duplicates
    By SLH in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2009, 11:18 PM
  6. [SOLVED] How do I Transpose without copying duplicates?
    By TheRook in forum Excel General
    Replies: 1
    Last Post: 05-17-2006, 04:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1