Macro Keyboard Shortcuts Not Working After Moving Macros From Personal Workbook

[sbf2014]

Hi everyone,

I recently followed these instructions (found online) for moving some Macros from my Personal workbook to a workbook that is shared with other users on our network drive:

It is not uncommon to place frequently used macros in the Personal.xls workbook. By placing them there, you are able to have the macros available all the time while you are using Excel. At some point, however, you may want to move the macros to a different workbook. For instance, you may want to place them in a workbook so they are easily accessible by anyone else opening the workbook.

To move macros from the Personal.xls workbook to a different workbook, follow these general steps:

1. Make sure the workbook that is the target of your macro transfer operation is loaded.
2. Unhide the Personal.xls file by choosing Unhide from the Window menu.
3. Press Alt+F11 to display the VBA editor.
4. Using the Project window, display the macros that you want to move.
5. Select (highlight) and cut (Ctrl+X) the macros from their original location in Personal.xls.
6. Using the Project window, display the module in the workbook where you want the macros to be. (If there is not an existing module in the workbook, you may need to create one.)
7. Paste (Ctrl+V) the macros in the module.
8. Close the VBA editor.
9. Hide the Personal.xls file by choosing Hide from the Window menu.
10. Close and save the workbooks.

It should be noted that when you move the location of the macros, the address by which they are called and invoked is also changed. Thus, if you have any menu items or toolbar buttons that were used to run the macros, these will need to be changed to point to the new location.


The problem is that now my keyboard shortcuts are not working. The author mentioned that this might happen at the bottom of the instructions, but s/he does not say how to point the shortcuts to the new location.

Here is my macro:

Sub Current_ICOS()
'
' Current_ICOS Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Current_ICOS"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("_1d__Current_Period_ICOS_Report").Select
Rows("1:1").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Current_ICOS").Select
Rows("1:1").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "null"
Range("A3").Select
ActiveCell.FormulaR1C1 = "201200"
Range("A4").Select
ActiveCell.FormulaR1C1 = "201201"
Range("A3:A4").Select
Selection.AutoFill Destination:=Range("A3:A15"), Type:=xlFillDefault
Range("A3:A15").Select
Range("A16").Select
ActiveCell.FormulaR1C1 = "201301"
Range("A17").Select
ActiveCell.FormulaR1C1 = "201302"
Range("A16:A17").Select
Selection.AutoFill Destination:=Range("A16:A27"), Type:=xlFillDefault
Range("A16:A27").Select
ActiveWindow.SmallScroll Down:=6
Range("A28").Select
ActiveCell.FormulaR1C1 = "201401"
Range("A29").Select
ActiveCell.FormulaR1C1 = "201402"
Range("A30").Select
ActiveCell.FormulaR1C1 = "201403"
Range("A31").Select
ActiveWindow.SmallScroll Down:=-15
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX(_1d__Current_Period_ICOS_Report!R2C2:R1000000C18,MATCH(Current_ICOS!RC1,_1d__Current_Period_ICOS_Report!R2C1:R1000000C1,0),MATCH(Current_ICOS!R1C,_1d__Current_Period_ICOS_Report!R1C2:R1C18,0)),"""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30"), Type:=xlFillDefault
Range("B2:B30").Select
Selection.AutoFill Destination:=Range("B2:R30"), Type:=xlFillDefault
Range("B2:R30").Select
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.NumberFormat = "#,##0"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:R30").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:R1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Font.Bold = True
Range("A2:A30").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Font.Bold = True
Range("B1").Select
Range("B2:R30").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B1").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B2").Select
ActiveWindow.SmallScroll Down:=6
Range("B32").Select
ActiveCell.FormulaR1C1 = "1000"
Range("B32").Select
Selection.Copy
Range("B2:R30").Select
Range("B30").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=6
Range("B32").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B2:R30").Select
Range("B30").Activate
ActiveWindow.DisplayZeros = False
ActiveWindow.SmallScroll ToRight:=-4
Columns("B:R").Select
Selection.ColumnWidth = 6
Range("Q1").Select
ActiveCell.FormulaR1C1 = "13-24"
Range("R1").Select
ActiveCell.FormulaR1C1 = "25+"
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B4").Select
ActiveCell.FormulaR1C1 = "Lag from Date of Death to Received Date ->"
With ActiveCell.Characters(Start:=1, Length:=42).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B4").Select
Selection.Font.Bold = True
Range("P4").Select
ActiveCell.FormulaR1C1 = "*Values in thousands"
With ActiveCell.Characters(Start:=1, Length:=20).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("P4").Select
Selection.Font.Bold = True
Range("A1").Select
ActiveCell.FormulaR1C1 = "CURRENT ICOS"
With ActiveCell.Characters(Start:=1, Length:=12).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A1").Select
Selection.Font.Bold = True
Range("D10").Select
ActiveWindow.SmallScroll Down:=12
Range("B36").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-30]C:R[-2]C)"
Range("C36").Select
ActiveCell.FormulaR1C1 = "GRAND TOTAL"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B36:C36").Select
Selection.Font.Bold = True
Range("I36").Select
ActiveWindow.SmallScroll Down:=-30
Prior_ICOS
New_ICOS
Paid_Claims_From_ICOS
New_Paid_Claims
Total_Paid_Claims
Change_in_ICOS

End Sub

Can anyone offer some advice on how to fix my keyboard shortcuts?

I would be so grateful!!!

Thanks,

sbf2014

 

[sijpie]

Open the shared workbook. Press Alt-F8. You will see the macros listed. Click (once) on a macr you want a shortcut for, then click on the Options... button. Enter the shortcut combination., click OK. Do this for all the macros as required, then save the workbook. Now the shortcuts will work again.