Excel Macro Not Working

Rrave26
Rrave26 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
When you open the XLSX file and press the shortcut, is the XLSM file also open?  If so that seems to work here.


»bp

Author

Commented:
No the XLSM file is not open.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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

Author

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?
Test your restores, not your backups...
Top Expert 2016
Commented:
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

Author

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?
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
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
Bill PrewTest your restores, not your backups...
Top Expert 2016

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?
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

Author

Commented:
Thanks for your help with this.  I got it working.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial