+ Reply to Thread
Results 1 to 9 of 9

Simple Copy/Paste Macro with Duplicate Prevention.

  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 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
    27,331

    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
    Please Login or Register  to view this content.


    Then your macro is

    Please Login or Register  to view this content.
    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

    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 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
    27,331

    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 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
    27,331

    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 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
    27,331

    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