Trying to assign macro short from VBA

[jack29]

Hi ,

I wanted to assign a permanent Shortcut key in VBA. rather than adding from macros --> options every day. likewise, I searched across and added this code. when I click Ctrl+Shift+Y, in should reset filter or clear some fields etc.. but that is failing to get performed. Please kindly let me know how to correct this. I have a few more macros every day it's difficult to assign the shortcut keys for each and also do not want to save the file as XLSM, so usually I would prefer to keep it as XLSX and in One Drive.

below is code

Option Explicit
Public Sub CreateShortcut()
Application.OnKey "+^{Y}", "Clear_Rule_Filter"
End Sub

Sub Clear_Rule_Filter()
'
' Clear_Rule_Filter Macro
'
'
ActiveSheet.Range("$I$1:$N$4000").AutoFilter Field:=6
End Sub

 

[jack29]

Hi ,

I did not know after writing this code I had not run the code using the play or compile button instead was trying to directly hit ctrl+shift+Y in my excel but it failed to clear up. However, after running the code once, it gets assigned then it started working. need to see this after restart if i just import macro would it assign or again need to compile.

Thanks,
Sukhen (Self Solved)