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("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
Rrave26Asked:
Who is Participating?
 
Bill PrewCommented:
One approach is to place the macro(s) into the PERSONAL.XLSB file on the employees computer.  That file is loaded when Excel is launched and the macros would be available in any workbook being used.  Some info on that.



»bp
0
 
Bill PrewCommented:
When you open the XLSX file and press the shortcut, is the XLSM file also open?  If so that seems to work here.


»bp
0
 
Rrave26Author Commented:
No the XLSM file is not open.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Bill PrewCommented:
Okay, then having just the XLSX file open will never work, since that macro "doesn't exist" from Excel's perspective.

When the new employee is going to work on a new XLSX file, could the process be to open the XLSM file that just has the macro(s) in it, so that they can use them on the newly created sheets?


»bp
0
 
Rrave26Author Commented:
I suppose he could do that.  Is there another way that this macro can be present/available each time he opens a new excel worksheet?  That way it can just be run on demand?
0
 
Rrave26Author Commented:
Thank you.  So let me recap what I am hearing.  I can create a blank workbook, add the vb code to it save it as a personal workbook and save it with a file name as an XLSM file.   I can email that file to my co worker he can open it save the file on his machine and then it will be active when he opens the source data and runs the macro by using the shortcut keys?
0
 
ShumsDistinguished Expert - 2017Commented:
Alternatively you can have macro enabled file to run the code on Summary file.

  1. Create a Dummy Folder on your Desktop.
  2. Download both attached file in Dummy Folder.
  3. Open Macro File.
  4. Click on Clean Up MIM Summary, it will prompt you to select file which needs formatting.
  5. Select file "Monthly MIM Summary 1.xlsx". It will run the code on selected file, save the file and close it.
  6. Re-open the file "Monthly MIM Summary 1.xlsx" to see if macro has ran successfully.

Hope this helps...
MacroFile.xlsm
Monthly-MIM-Summary-1.xlsx
0
 
Bill PrewCommented:
Thank you.  So let me recap what I am hearing.  I can create a blank workbook, add the vb code to it save it as a personal workbook and save it with a file name as an XLSM file.   I can email that file to my co worker he can open it save the file on his machine and then it will be active when he opens the source data and runs the macro by using the shortcut keys?
Yes, although I am not sure that you can just send your file to them, I seem to remember that you couldn't do that, but would have to research.  They may have to recreate the process you use there, once you give them the macro code.


»bp
0
 
Rrave26Author Commented:
Thanks for your help with this.  I got it working.
0
 
Bill PrewCommented:
Great, glad you got a solution.

Just curious, which way did you use there, and anything interesting you learned in taking that approach that could be useful to future readers?


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.