+ Reply to Thread
Results 1 to 9 of 9

Simple Copy/Paste Macro with Duplicate Prevention.

Hybrid View

  1. #1
    Registered User
    Join Date
    08-28-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Simple Copy/Paste Macro with Duplicate Prevention.

    Hi,

    I am very new to macros (and excel to be honest) but I am working on a macro to copy and paste data into a new worksheet.

    Basically I have an excel file which the user chooses some variables in dropdown list cells that result in a concatenate being formed and then I have designed a button, which when pressed sends the concatenate to the next available row in a different tab. However it is important that there are no duplicate values sent to this destination and I want an error message to occur upon the user pressing the button if a duplicate concatenate is made (and not let them paste it). So far I have a working macro to copy and paste the concatenate to the new tab, but I am stumped at creating the error message part.

    This is what I have so far:

    Sub GENERATOR()

    Range("L12").Copy

    Sheets("OUTPUT").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    End Sub



    (L12 being the concatenate cell and OUTPUT being the destination tab where I can't have any duplicates)

    Thank you so much in advance for any help given.

    Best Regards,

    Chris

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    Hi, and welcome to the forum.

    The way I usually trap this situation is to use a MATCH() function. So for example in your case in another cell named say 'Check" add

    Formula: copy to clipboard
    =IF(ISERROR(MATCH(L12,Output!A:A,False)),"OK","Duplicate")


    Then your macro is

    Sub GENERATOR()
    
       IF Range("Check") = "Duplicate" Then
          MsgBox "There is a duplicate on the Output sheet. This record will not be added."
          Exit Sub
       End If
    
       Range("L12").Copy
       Sheets("OUTPUT").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial(xlPasteValues)
    
    End Sub
    Incidentally please remember to use code tags around any code you include as per the forum rules.
    Last edited by Richard Buttrey; 08-28-2013 at 06:20 AM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-28-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    Thank you for a speedy response!

    When I run this new macro I get this:

    Run-time error '1004':

    Method 'Range' of object '_Global' failed


    Any ideas?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    Hi,

    I think XL2010 is a bit fussier about referencing objects.
    Which debug line is highlighted?

    If the IF...line then
    IF SheetCodeName.Range("Check") = "Duplicate" Then

    If the .Copy line then
    SheetCodeName.Range("L12").Copy

    Note SheetCodeName is the VBE Sheet Code names not the tab name. Always use SheetCode names wherever possible in case a user changes a tab name.

    If this doesn't fix the problem upload the workbook

  5. #5
    Registered User
    Join Date
    08-28-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    That works brilliantly thank you!

    But with one small issue - the 'check' cell doesn't say "Ok" or "Duplicate" it just says false and I wouldn't mind this but its going to be used by a client so I would like it to say this.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    Hi,

    On the face of it I can't see why you would get FALSE. Can you upload the workbook, anonymised if necessary. We don't need zillions of rows just a representative sample.

  7. #7
    Registered User
    Join Date
    08-28-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    See attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-28-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    Apologies, with the one above, change the material type in D13 to 'Steel' and the macro just needs a tweak. But do you see where it says 'False' in J13?

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Simple Copy/Paste Macro with Duplicate Prevention.

    Hi,

    D13 already contained the word 'Steel'
    There was no formula in J13 hence I don't see 'False'

    In the attached I've added the J13 formula and also given it the range name 'Check'. I've also changed the macro 'Generator'.
    Attached Files Attached Files

+ 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. Simple copy/paste macro
    By Katsdog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2013, 11:49 AM
  2. Replies: 3
    Last Post: 04-04-2013, 04:57 PM
  3. [SOLVED] Simple copy and paste macro- Paste special help needed.
    By hernancrespo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2012, 07:02 AM
  4. [SOLVED] Simple copy and paste macro
    By hernancrespo in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-08-2012, 08:16 AM
  5. Simple Copy/Paste Macro???
    By kwright90 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2007, 01:22 PM

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