mouse select and filter in another tab Macro

Question

I would like to create a macro where I can use mouse to select a name in tab "Capacity", and by pressing hot key Ctrl+Shift+Z, macro will activate and search this name in tab "Allocation" in a name filter

I am stucked with how to use macro to copy what I selected and paste this to the filter criteria

I have tried with this

   Sub Macro1()
    '
    ' Macro1 Macro
    ' Resource allocaiton breakdown
    '
    ' Keyboard Shortcut: Ctrl+Shift+Z
    '
            Selection.Copy
            Sheets("Allocation").Select
            ActiveSheet.Range("$A$9:$FL$529").AutoFilter Field:=6
            ActiveSheet.Range("$A$9:$FL$529").AutoFilter Field:=1
            ActiveSheet.Range("$A$9:$FL$529").AutoFilter Field:=6, Criteria1:="Target.Value"

End Sub

but it's not working,
I am fairly new to VBA
please advise

score 0 · Accepted Answer · answered Jul 16 '18 at 06:20

0

You should avoid using Select in Excel VBA.
You cannot "copy" a cell value into a filter criteria instead you set the criteria to that value by using the cell value as Criteria1

So something like this should work:

Option Explicit

Public Sub FilterForSelectedValue()
    Dim SelectedFilter As Range
    Set SelectedFilter = Selection

    Worksheets("Allocation").Range("$A$9:$FL$529").AutoFilter Field:=6, Criteria1:=SelectedFilter.Value

End Sub

answered Jul 16 '18 at 06:20

Pᴇʜ

56,719
10
49
73

thanks for replying PEH, but the name is selected from another tab "capacity", and filter is in "Allocation", would this still work? – Elgatoo Jul 16 '18 at 06:31
@Elgatoo did you even try it? As you can see it filters a range in `Worksheets("Allocation")`. And it uses `Selection` on what ever sheet is selected at the moment you run the code. – Pᴇʜ Jul 16 '18 at 06:34
Tried it, and added Sheets("Allocation").Select at the end of it, it seems working!!! thank you so much, and also if i want to assign hot keys to this, will it be the same? – Elgatoo Jul 16 '18 at 06:43
@Elgatoo You can assign a hotkey to almost any macro you write. Why don't you try it before you ask? – Pᴇʜ Jul 16 '18 at 06:54

mouse select and filter in another tab Macro

1 Answers1