Solved

Macro to run from selection made using data validation list

Posted on 2016-08-17
3
39 Views
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()

    Columns("AW:AX").Select
    Selection.Copy
    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), _
        Header:=xlYes
    Sheets("Data").Select
    Range("Table_ExternalData_18[[#Headers],[IFA First Name]]").Select

End Sub

Open in new window

0
Comment
Question by:route217
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
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("Data").Columns("AW:AX").Copy
    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), _
                                                                 Header:=xlYes
    ''///  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

0
 

Author Comment

by:route217
ID: 41760588
thanks for the feedback...
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41761263
Pleased to help
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now