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