Macro works in my workbook, but not when I send to someone else

[Desperationmode]

Hi Everyone,

Any help here would be greatly appreciated! I've tried checking trust settings, etc. on other people's computers, they're set to allow macros. I'm getting run-time error 438 when i run this code on other's computers. Any idea why? I red fonted/underlined the line of code that the debugger is highlighting too.

Thanks so much in advance for any help!

---------------------------------------------------
Sub For_Intrepid()
'
' For_Intrepid Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Sheets("Intrepid Status Update").Select
    Sheets("Intrepid Status Update").Copy Before:=Sheets(2)
    Sheets("Intrepid Status Update (2)").Select
    Sheets("Intrepid Status Update (2)").Name = "For Intrepid"
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter
    Range("B:B,E:H,J:J,K:K,N:N").Select
    Range("N1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("G2").Select
    ActiveWorkbook.Worksheets("For Intrepid").AutoFilter.Sort.SortFields.Clear
   [U][COLOR=#ff0000] ActiveWorkbook.Worksheets("For Intrepid").AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal[/COLOR][/U]
    With ActiveWorkbook.Worksheets("For Intrepid").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("H2").Select
    ActiveWorkbook.Worksheets("For Intrepid").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("For Intrepid").AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("H2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("For Intrepid").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Range("A1").Select
    Cells.Find(What:="portco", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Rows("14:14").Select
    Range("H14").Activate
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "Portfolio Company Transactions"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = "Active"
    Range("B15:B16").Select
    Range("B16").Activate
    Selection.Font.Bold = True
    Range("B17").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Find(What:="to be invoiced", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Rows("20:20").Select
    Range("G20").Activate
    Selection.Insert Shift:=xlDown
    Range("F20").Select
    Selection.End(xlToLeft).Select
    Range("B20").Select
    ActiveCell.FormulaR1C1 = "To be invoiced"
    Range("B20").Select
    Selection.Font.Bold = True
    Range("A20").Select
    Selection.End(xlUp).Select
    Rows("3:3").Select
    Range("B3").Activate
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Fund Transactions"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "Active"
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Find(What:="to be invoiced", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Rows("11:11").Select
    Range("G11").Activate
    Selection.Insert Shift:=xlDown
    Selection.End(xlToLeft).Select
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "To be invoiced"
    Range("B11").Select
    Selection.Font.Bold = True
End Sub
 
 
 
 
 
 
 
 
 
 
 
Sub Formatting_For_Intrepid()
'
' Formatting_For_Intrepid Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    ActiveWindow.FreezePanes = False
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Intrepid Transaction Status as of:"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("B3:C3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Columns("B:B").EntireColumn.AutoFit
    Range("C3").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "Active Life Name"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "Intrepid Life Team"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "Expected Signing / Completion Date"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "Size"
    Columns("G:H").Select
    Range("G6").Activate
    Selection.EntireColumn.Hidden = True
    Range("B7:F20").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Rows("21:21").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Rows("21:21").EntireRow.AutoFit
    Columns("C:C").ColumnWidth = 58.33
    Cells.Select
    Cells.EntireRow.AutoFit
    Range("C12").Select
    ActiveWindow.DisplayGridlines = True
    ActiveWindow.DisplayGridlines = False
    Range("B7:F30").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B22:F30").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("C14").Select
    Columns("D:D").ColumnWidth = 35.67
    Rows("6:6").EntireRow.AutoFit
    Rows("6:6").EntireRow.AutoFit
    Columns("D:D").ColumnWidth = 58.33
    Rows("6:6").RowHeight = 12.6
    Rows("6:6").Select
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A9").Select
    Selection.Font.Bold = True
    Range("A9").Select
    Columns("A:A").EntireColumn.AutoFit
    Range("A9").Select
    Selection.ClearContents
    Range("B6").Select
    Selection.Copy
    Range("B3:C3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C3").Select
    Selection.NumberFormat = "m/d/yyyy"
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("B3").Select
End Sub

 

[Fluff]

When posting code please use code tags, the # icon in the reply window.

As to your problem, change .Add2 to .Add
You will need to do that for every instance of Add2

 

[Desperationmode]

Fluff - thanks so much! Apologies for the formatting inconvenience, I'm a newbie!

I appreciate your help and quick response

 

[Fluff]

Glad to help & thanks for the feedback

 

[fozman]

Hi,
I'm also having this issue & the tip to replace ".add2" with ".add" worked...

Until I emailed it to a client (who uses Excel 2013) & the macro appears to have "regenerated" with the ".add2" as, when he tries to run the macro, he gets a runtime error & the same lines (with the ".add2") highlighted as shown on the original post.

So between my laptop, Outlook & his computer, it would appear that Excel is adding the "2"... frustrating to say the least.

So maybe I need to find a different way to sort a table!

 

[RoryA]

Excel will not alter the code between versions, so something else must be going on.

 

[fozman]

Excel will not alter the code between versions, so something else must be going on.

Yup, that's my thoughts... however, with the evidence before my eyes...

So, instead of using the code generated by the Macro recorder, I've actually researched some "proper" code & that seems to have done the trick (and it's a gazillion lines shorter, too!!)