Hi,
I am completely new to VBA so please forgive the poor us of technical language and code you are about to see. I keep getting an error when running the macro saying subscript out of reach. Where am I going wrong in my code?
Sub Complete()
Dim rng As Range
Dim calcmode As Long
Dim myArr As Variant
Dim I As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
myArr = Array("Withdrawal", "Event", "Dividend")
For I = LBound(myArr) To UBound(myArr)
With ActiveSheet
.AutoFilterMode = False
.Range("B1:B" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
Set rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next I
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
Cells.Replace What:="Sale", Replacement:="S", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Purchase", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Portfolio Name"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Destination 2"
Range("A2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("BBU Macro.xlsm").Activate
Cells.Select
ActiveSheet.Paste
Sheets("Settings").Select
Application.Run "ConnectChartEvents"
End Sub
Best,
Jack