troubleshooting Question

Excel Macro Not Working

Avatar of Rrave26
Rrave26 asked on
VB ScriptMicrosoft OfficeVBAMicrosoft ExcelMicrosoft Applications
10 Comments2 Solutions434 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros