none
Macro Shortcut not working when pasted into a different workbook??? RRS feed

  • Question

  • Hello everyone.

    I have a couple of simple filter macros that I "recorded". It simply hides columns and filters off of one column and the other simply un-hides everything.

    When I try and paste this into another workbook or my template file the keyboard shortcut is not working. Any thoughts? Is this because it's a recorded macro rather than a programmed one?

    I'll have to add that all worksheets are named the same.

    '
    ' Keyboard Shortcut: Ctrl+q
    '
        Columns("A:A").Select
        Selection.EntireColumn.Hidden = True
        ActiveSheet.Range("$B$1:$AK$6000").AutoFilter Field:=3, Criteria1:="AI"
        Columns("D:D").Select
        Selection.EntireColumn.Hidden = True
        Columns("F:F").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:N").Select
        Selection.EntireColumn.Hidden = True
        Columns("P:AD").Select
        Selection.EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        Columns("AF:AH").Select
        Selection.EntireColumn.Hidden = True
        Range("B2").Select
        ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort.SortFields.Add(Range( _
            "B1:B6000"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
            = RGB(112, 48, 160)
        With ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort.SortFields.Add(Range( _
            "B1:B6000"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
            = RGB(255, 0, 102)
        With ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort.SortFields.Add(Range( _
            "B1:B6000"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
            = RGB(255, 0, 0)
        With ActiveWorkbook.Worksheets("NEOCOP").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    Sub NEOCOPunfilter()
    '
    ' NEOCOPunfilter Macro
    '
    ' Keyboard Shortcut: Ctrl+w
    '
        ActiveWindow.SmallScroll Down:=-18
        Cells.Select
        Selection.EntireColumn.Hidden = False
        Range("C7").Select
    End Sub


    Tuesday, July 24, 2018 6:36 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, July 25, 2018 2:37 AM
  • A recorded macro can also works like the programmed macro.Perhaps your paste is not all you record,i think you could check it again?

    Regards,

    Yuki

    Wednesday, July 25, 2018 2:56 AM
  • Hello stillanoob,

    As far as I know, Copy macro code could not copy its ShortCut at the same time. You have to reset ShortCut for the copied macro manually.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 25, 2018 9:31 AM
  • Probably you didn't link shortcut to macro.

    Try this. Press ALT+F8, if you see that macro try running it. If it works great.

    Then you can link it to shortcut. Again open Macros (ALt+F8) select macro, click options and enter shortcut key...

    Hope this helps.

    Kind regard

    Friday, August 3, 2018 9:23 AM