Macro to run from selection made using data validation list

Posted on 2016-08-17
Last Modified: 2016-08-18
Hi Experts Using Excel 2013

i have the following vba code which may need tidying up (see below)

i want to run the below macro based on the data validation list in cell B3 worksheet "Overall". I also want to remove any blank rows in the data set after the duplication element has been removed.

Sub CopyandPasteValues()

    Sheets("Unique List").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$B$1928").RemoveDuplicates Columns:=Array(1, 2), _
    Range("Table_ExternalData_18[[#Headers],[IFA First Name]]").Select

End Sub

Open in new window

Question by:route217
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 20

Accepted Solution

Roy Cox earned 500 total points
ID: 41760573
There's no indication what the Data Validation list is or what it is supposed to do. Provide more information and a sample workbook.

This code is more efficient
Option Explicit

Sub CopyandPasteValues()
''/// assumes Sheets("Data") is the sheet to copy from
''/// if not use ActiveSheet
    Sheets("Unique List").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                          :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Unique List").Range("$A$1:$B$1928").RemoveDuplicates Columns:=Array(1, 2), _
    ''///  I see no point in this. Is Data the starting point? If so then my code does not
    ''///change sheets so you can delete this
    '    Sheets("Data").Select
    '    Range("Table_ExternalData_18[[#Headers],[IFA First Name]]").Select

End Sub

Open in new window


Author Comment

ID: 41760588
thanks for the feedback...
LVL 20

Expert Comment

by:Roy Cox
ID: 41761263
Pleased to help

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question