jordanburch
Active Member
- Joined
- Jun 10, 2016
- Messages
- 443
- Office Version
- 2016
VBA Code:
Sub reconciliation()
'
' reconciliation Macro
'
'
Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select
If ActiveSheet.AutoFilterMode Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Else
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End If
Range("AR12").Select
ActiveSheet.Range("$A$1:$Ax$6000").AutoFilter Field:=34, Criteria1:= _
"5 - ALC "
ActiveSheet.Range("$A$1:$Ax$6000").AutoFilter Field:=36, Criteria1:="="
Range("as2:as6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE("""",RC[-44],RC[-40])"
Range("at2:at6000").SpecialCells(xlCellTypeVisible).Formula = "=+TRIM(RC[-31])"
Range("au2:au6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RIGHT(RC[-1],4))"
Range("av2:av6000").SpecialCells(xlCellTypeVisible).Formula = "=+SUMIF(C[-1],RC[-1],C[-38])"
Range("aw2:aw6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RC[-1])"
Sheets("").Select
Range("BD2").Select
ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-50],RC[-47],RC[-45])"
Range("BD2").Select
Selection.AutoFill Destination:=Range("BD2:BD488"), Type:=xlFillDefault
Range("BD2:BD488").Select
Range("BE2").Select
ActiveCell.FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-42])"
Range("BE2").Select
Selection.AutoFill Destination:=Range("BE2:BE488")
Range("BE2:BE488").Select
Range("BF2").Select
ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-2],RC[-1])"
Range("BF2").Select
Selection.AutoFill Destination:=Range("BF2:BF488")
Range("BF2:BF488").Select
Range("BG2").Select
ActiveCell.FormulaR1C1 = "=+MATCH(RC[-1],'FEB FY21 IDARRS'!C[-10],0)"
Range("BG2").Select
Selection.AutoFill Destination:=Range("BG2:BG488")
Range("BG2:BG488").Select
Columns("BF:BF").EntireColumn.AutoFit
Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select
Columns("AW:AW").ColumnWidth = 8.22
Columns("AW:AW").EntireColumn.AutoFit
Sheets("").Select
Range("BF3").Select
Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select
Range("ax2:ax6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],!C[8],0)"
Sheets("").Select
Range("BD2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+CONCATENATE("""",RC[-50],RC[-47],RC[-45])"
Range("BD2").Select
Selection.AutoFill Destination:=Range("BD2:BD488")
Range("BD2:BD488").Select
Range("BH2").Select
Selection.ClearContents
Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select
With ActiveSheet
If ActiveSheet.AutoFilterMode Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Else
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End If
End With
ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=50, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues
Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select
Range("aj2:aj6000").SpecialCells(xlCellTypeVisible).Formula = "COMPLETE"
Range("al2:al6000").SpecialCells(xlCellTypeVisible).Formula = ""
End Sub
here is the part it is erroring out on . seems like the range isnt activated or working right. Im not sure whats wrong though. ANy help is appreciated! 1004 autofilter method range of class failed is the error.
ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=50, Criteria1:="<>#N/A", _
Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues