Sub ProcessPayroll04()
'Remove all accounbt numbers with DUE in the description
Application.ScreenUpdating = False
With Sheets("Import")
.AutoFilterMode = False
With .Range("E1", .Range("E" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Due*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
On Error GoTo 0
End With
.AutoFilterMode = False
End With
'Remove all accountnumbers with Net Payroll Payable in the description
'With Sheets("report")
' .AutoFilterMode = False
' With .Range("E1", .Range("E" & Rows.Count).End(xlUp))
' .AutoFilter 1, "*Net Payroll Payable*"
' On Error Resume Next
' .Offset(1).SpecialCells(12).EntireRow.Delete
' On Error GoTo 0
' End With
' .AutoFilterMode = False
' End With
'Clear BU04Data
Sheets("BU04Data").Select
Range("A1:I150").Select
Selection.ClearContents
Range("A1").Select
Sheets("GeneralJournal").Select
Range("H5").Select
Selection.ClearContents
'Copy Data from Payroll Report to BU04
Dim Ws As Worksheet
Set Ws = Worksheets("BU04Data")
With Worksheets("Import")
.Range("A1:I1").AutoFilter 1, "4"
.AutoFilter.Range.Offset(1).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(0)
.AutoFilterMode = False
End With
'Copy BU04 Data to Atlas Upload
With Sheets("GeneralJournal").ListObjects("table1").DataBodyRange
.Rows("2:" & .Rows.Count).Delete
End With
With Sheets("BU04Data")
.Range("L1:W" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
End With
Worksheets("GeneralJournal").Range("B17").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard
Application.CutCopyMode = False
'On Error Resume Next
Sheets("GeneralJournal").Select
With Range("Q17:Q" & Cells(Rows.Count, "B").End(xlUp).Row)
.Formula = "=IF([@AccountType]=""Ledger"",CONCATENATE([@[Main account]],""-"",[@BusinessUnit],""-"",[@Department],""-"",[@CostCenter],""-"",[@CIP],""-""),IF(OR([@AccountType]=""Customer"",[@AccountType]=""Vendor"",[@AccountType]=""Fixedassets""),SUBSTITUTE([@[Main account]],""-"",""\-"",1),[@[Main account]]))"
End With
'Delete Blank Rows if No Data in cells in Column B
'On Error Resume Next
With Sheets("GeneralJournal").ListObjects("Table1").ListColumns("Main account").DataBodyRange
.Value = .Value
On Error Resume Next
.SpecialCells(xlBlanks).Delete
On Error GoTo 0
End With
Range("B17").Select
Application.ScreenUpdating = True
End Sub