Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Macro to run from selection made using data validation list

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
route217
Asked:
route217
  • 2
1 Solution
 
Roy CoxGroup Finance ManagerCommented:
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
 
route217Author Commented:
thanks for the feedback...
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now