asked on
Excel Macro Not Working
I created a simple Macro that cleans some data from an excel file that generated from ServiceNow every month. The Macro simply formats some fields, creates columns, splits data in one column to two columns and sorts the data. Here is the current code, I know the code isn't optimized but I am under a time constraint to get this done.
Sub MIMCleanup()
'
' MIMCleanup Macro
'
' Keyboard Shortcut: Ctrl+k
'
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yyyy"
Columns("B:B").Select
ActiveWorkbook.Worksheets(
ActiveWorkbook.Worksheets(
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(
.SetRange Range("A1:O16")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuo
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("E1").Select
ActiveCell.FormulaR1C1 = "Priority 2"
Columns("F:F").Select
Selection.Replace What:="Global", Replacement:="NA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("M:M").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=9
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("N:N").Select
Selection.TextToColumns Destination:=Range("N1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("P:P").Select
Selection.TextToColumns Destination:=Range("P1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("S:S").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("R:R").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=5
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("T:T").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("W:W").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("V:V").Select
Selection.TextToColumns Destination:=Range("V1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Range("W:W,U:U,S:S,Q:Q").S
Range("Q1").Activate
ActiveWindow.SmallScroll ToRight:=-7
Range("W:W,U:U,S:S,Q:Q,O:O
Range("I1").Activate
ActiveWindow.SmallScroll ToRight:=-3
Selection.NumberFormat = "[h]:mm"
Range("H:H,J:J,L:L,N:N,P:P
Range("P1").Activate
ActiveWindow.SmallScroll ToRight:=6
Range("H:H,J:J,L:L,N:N,P:P
Range("T1").Activate
ActiveWindow.SmallScroll ToRight:=3
Range("H:H,J:J,L:L,N:N,P:P
Range("V1").Activate
Selection.NumberFormat = "mm/dd/yyyy"
Range("W1,U1,S1,Q1,O1").Se
Range("O1").Activate
ActiveWindow.SmallScroll ToRight:=-5
Range("W1,U1,S1,Q1,O1,M1,K
Range("K1").Activate
ActiveWindow.SmallScroll ToRight:=-5
Range("W1,U1,S1,Q1,O1,M1,K
Range("I1").Activate
ActiveCell.FormulaR1C1 = "Time"
Range("W1,U1,S1,Q1,O1,M1,K
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Range("I1").Select
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Range("M1").Select
ActiveSheet.Paste
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=8
Range("Q1").Select
ActiveSheet.Paste
Range("S1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("U1").Select
ActiveSheet.Paste
Range("W1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
Range("H1").Select
ActiveCell.FormulaR1C1 = "Outage Start"
Range("J1").Select
ActiveCell.FormulaR1C1 = "IMT Engaged"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Crisis Call Start"
Range("N1").Select
ActiveWindow.SmallScroll ToRight:=9
Range("P1").Select
ActiveCell.FormulaR1C1 = "1st Comm sent"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Successful Health Check"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Service Available"
Range("V1").Select
ActiveCell.FormulaR1C1 = "Crisis Call End"
Range("W8").Select
End Sub
When I open the excel file that gets generated each month, "Monthly MIM Summary" and try to run the macro using the shortcut, the macro will not execute, but if I go into VB and select Run from the menu bar it executes without any issues. At first I thought the shortcut wasn't running because the source file I was opening was in [Read-Only]. So I saved the source file as "Monthly MIM Summary 1" and tried to use the macro using the shortcut and it still did not execute, but again if I go into VB and select Run from the menu bar it executes without any issues.
My goal is that I want to share this macro with a new employee who will be taking over this task from me and I would like him to be able to open the source file "Monthly MIM Summary" hit the shortcut command and the macro is run for him.
What am I missing here?
Copy-of-Monthly-MIM-Summary-1.xlsx
Copy-of-Monthly-MIM-Summary-macro.xlsm
Sub MIMCleanup()
'
' MIMCleanup Macro
'
' Keyboard Shortcut: Ctrl+k
'
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yyyy"
Columns("B:B").Select
ActiveWorkbook.Worksheets(
ActiveWorkbook.Worksheets(
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(
.SetRange Range("A1:O16")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuo
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("E1").Select
ActiveCell.FormulaR1C1 = "Priority 2"
Columns("F:F").Select
Selection.Replace What:="Global", Replacement:="NA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("M:M").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=9
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("N:N").Select
Selection.TextToColumns Destination:=Range("N1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("P:P").Select
Selection.TextToColumns Destination:=Range("P1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("S:S").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("R:R").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
ActiveWindow.SmallScroll ToRight:=5
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("T:T").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Columns("W:W").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe
Columns("V:V").Select
Selection.TextToColumns Destination:=Range("V1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
Range("W:W,U:U,S:S,Q:Q").S
Range("Q1").Activate
ActiveWindow.SmallScroll ToRight:=-7
Range("W:W,U:U,S:S,Q:Q,O:O
Range("I1").Activate
ActiveWindow.SmallScroll ToRight:=-3
Selection.NumberFormat = "[h]:mm"
Range("H:H,J:J,L:L,N:N,P:P
Range("P1").Activate
ActiveWindow.SmallScroll ToRight:=6
Range("H:H,J:J,L:L,N:N,P:P
Range("T1").Activate
ActiveWindow.SmallScroll ToRight:=3
Range("H:H,J:J,L:L,N:N,P:P
Range("V1").Activate
Selection.NumberFormat = "mm/dd/yyyy"
Range("W1,U1,S1,Q1,O1").Se
Range("O1").Activate
ActiveWindow.SmallScroll ToRight:=-5
Range("W1,U1,S1,Q1,O1,M1,K
Range("K1").Activate
ActiveWindow.SmallScroll ToRight:=-5
Range("W1,U1,S1,Q1,O1,M1,K
Range("I1").Activate
ActiveCell.FormulaR1C1 = "Time"
Range("W1,U1,S1,Q1,O1,M1,K
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Range("I1").Select
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Range("M1").Select
ActiveSheet.Paste
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=8
Range("Q1").Select
ActiveSheet.Paste
Range("S1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("U1").Select
ActiveSheet.Paste
Range("W1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
Range("H1").Select
ActiveCell.FormulaR1C1 = "Outage Start"
Range("J1").Select
ActiveCell.FormulaR1C1 = "IMT Engaged"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Crisis Call Start"
Range("N1").Select
ActiveWindow.SmallScroll ToRight:=9
Range("P1").Select
ActiveCell.FormulaR1C1 = "1st Comm sent"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Successful Health Check"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Service Available"
Range("V1").Select
ActiveCell.FormulaR1C1 = "Crisis Call End"
Range("W8").Select
End Sub
When I open the excel file that gets generated each month, "Monthly MIM Summary" and try to run the macro using the shortcut, the macro will not execute, but if I go into VB and select Run from the menu bar it executes without any issues. At first I thought the shortcut wasn't running because the source file I was opening was in [Read-Only]. So I saved the source file as "Monthly MIM Summary 1" and tried to use the macro using the shortcut and it still did not execute, but again if I go into VB and select Run from the menu bar it executes without any issues.
My goal is that I want to share this macro with a new employee who will be taking over this task from me and I would like him to be able to open the source file "Monthly MIM Summary" hit the shortcut command and the macro is run for him.
What am I missing here?
Copy-of-Monthly-MIM-Summary-1.xlsx
Copy-of-Monthly-MIM-Summary-macro.xlsm
VB ScriptMicrosoft OfficeVBAMicrosoft ExcelMicrosoft Applications
Last Comment
Bill Prew