We help IT Professionals succeed at work.
Get Started

Excel Macro Not Working

431 Views
Last Modified: 2018-02-02
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("Page 1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Page 1").Sort.SortFields.Add Key:=Range("B2:B16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Page 1").Sort
        .SetRange Range("A1:O16")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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:=xlFormatFromLeftOrAbove
    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").Select
    Range("Q1").Activate
    ActiveWindow.SmallScroll ToRight:=-7
    Range("W:W,U:U,S:S,Q:Q,O:O,M:M,K:K,I:I").Select
    Range("I1").Activate
    ActiveWindow.SmallScroll ToRight:=-3
    Selection.NumberFormat = "[h]:mm"
    Range("H:H,J:J,L:L,N:N,P:P").Select
    Range("P1").Activate
    ActiveWindow.SmallScroll ToRight:=6
    Range("H:H,J:J,L:L,N:N,P:P,R:R,T:T").Select
    Range("T1").Activate
    ActiveWindow.SmallScroll ToRight:=3
    Range("H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V").Select
    Range("V1").Activate
    Selection.NumberFormat = "mm/dd/yyyy"
    Range("W1,U1,S1,Q1,O1").Select
    Range("O1").Activate
    ActiveWindow.SmallScroll ToRight:=-5
    Range("W1,U1,S1,Q1,O1,M1,K1").Select
    Range("K1").Activate
    ActiveWindow.SmallScroll ToRight:=-5
    Range("W1,U1,S1,Q1,O1,M1,K1,I1").Select
    Range("I1").Activate
    ActiveCell.FormulaR1C1 = "Time"
    Range("W1,U1,S1,Q1,O1,M1,K1,I1").Select
    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
Comment
Watch Question
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 2 Answers and 10 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE