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

D

Desperationmode

New Member
Joined
Jan 8, 2019
Messages
3
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!
Code: 
---------------------------------------------------
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
 
Last edited by a moderator:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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!
 
Upvote 0
Excel will not alter the code between versions, so something else must be going on.
 
Upvote 0
RoryA said:
Excel will not alter the code between versions, so something else must be going on.
Click to expand...

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!!)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
K
  • Solved
formating question
Replies
3
Views
324
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
453
CapRavOr
C
B
  • Solved
Object required error
Replies
2
Views
163
Boston_Paul
B

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top