Sub CashmacroNoselect()
' CashmacroNoselect Macro
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Columns("F:G").Cut
Range("X:Y").Insert
Columns("R:R").Cut
Range("X:X").Insert
Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("R:R").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:4").Delete Shift:=xlUp
Range("M:N,F:F,W:W,X:X,AB:AB").NumberFormat = "0"
Range("P:P,S:S,U:U,AE:AE").NumberFormat = "m/d/yy;@"
Range("Q:Q,R:R").NumberFormat = "[$-409]mmm-yy;@"
Columns("V:V").NumberFormat = "[$-409]d-mmm;@"
Range("Y:Y,T:T,Z:Z,AA:AA,AC:AC").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("AF:AF").NumberFormat = "0.0"
MaxRows = Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveSheet.Sort
.SetRange Range("A1:CY" & MaxRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("G:G").Find(What:="-", After:=Range("G1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Select
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).EntireRow.Delete Shift:=xlUp
MaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").FormulaR1C1 = "Clt ID"
Application.Workbooks.Open ("w:\Cashmacroref.xlsx")
wb.Activate
ws.Range("I2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("H2").Formula
Range("I2").AutoFill Destination:=Range("I2:I" & MaxRows), Type:=xlFillDefault
Range("I:I") = Range("I:I").Value
Columns("H:H").Delete Shift:=xlToLeft
ws.Range("I2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("I2")
ws.Range("I2").Formula = "=" & Range("I2").Value
Range("I2").AutoFill Destination:=Range("I2:I" & MaxRows), Type:=xlFillDefault
Range("I:I") = Range("I:I").Value
ws.Range("J2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("J2").Formula
ws.Range("J2").Formula = "=" & Range("J2").Value
Range("J2").AutoFill Destination:=Range("J2:J" & MaxRows), Type:=xlFillDefault
Range("J:J") = Range("J:J").Value
ws.Range("K2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("K2").Formula
ws.Range("K2").Formula = "=" & Range("K2").Value
Range("K2").AutoFill Destination:=Range("K2:K" & MaxRows), Type:=xlFillDefault
Range("K:K") = Range("K:K").Value
ws.Range("D2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("D2").Formula
ws.Range("D2").Formula = "=" & Range("D2").Value
Range("D2").AutoFill Destination:=Range("D2:D" & MaxRows), Type:=xlFillDefault
Range("D:D") = Range("D:D").Value
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("S2:S" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveSheet.Sort
.SetRange Range("A1:CY" & MaxRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -3).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Copy
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveSheet.Paste
ws.Range("R2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("R2").Formula
Range("R2").AutoFill Destination:=Range("R2:R" & MaxRows), Type:=xlFillDefault
Range("R:R") = Range("R:R").Value
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("Z2:Z" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveSheet.Sort
.SetRange Range("A1:CY" & MaxRows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("Z:Z").AutoFilter
ActiveSheet.Range("Z1:Z" & MaxRows).AutoFilter Field:=1, Criteria1:="<0", _
Operator:=xlAnd
Range("Z2").Select
Range(Selection, Selection.End(xlDown)).Cut
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Range("$Z$1:$Z$1047038").AutoFilter Field:=1
Range("Y1").FormulaR1C1 = "NSFAmt"
Selection.AutoFilter
ws.Range("X2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("Y2").Formula
Range("X2").AutoFill Destination:=Range("X2:X" & MaxRows), Type:=xlFillDefault
Range("X:X") = Range("X:X").Value
ws.Range("Q2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("Q2").Formula
Range("Q2").AutoFill Destination:=Range("Q2:Q" & MaxRows), Type:=xlFillDefault
Range("Q:Q") = Range("Q:Q").Value
Range("Q:Q").NumberFormat = "[$-409]mmm-yy;@"
Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("O2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("O2").Formula
Range("O2").AutoFill Destination:=Range("O2:O" & MaxRows), Type:=xlFillDefault
Range("O:O") = Range("O:O").Value
Columns("P:P").Delete Shift:=xlToLeft
Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("U2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("U2").Formula
Range("U2").AutoFill Destination:=Range("U2:U" & MaxRows), Type:=xlFillDefault
Range("U:U") = Range("U:U").Value
ws.Range("W2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("W2").Formula
Range("W2").AutoFill Destination:=Range("W2:W" & MaxRows), Type:=xlFillDefault
Range("W:W") = Range("W:W").Value
ws.Range("X2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("X2").Formula
Range("X2").AutoFill Destination:=Range("X2:X" & MaxRows), Type:=xlFillDefault
Range("X:X") = Range("X:X").Value
ws.Range("AH2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AH2").Formula
ws.Range("AH2").Formula = "=" & Range("AH2").Value
Range("AH2").AutoFill Destination:=Range("AH2:AH" & MaxRows), Type:=xlFillDefault
Range("AH:AH") = Range("AH:AH").Value
ws.Range("AI2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AI2").Formula
ws.Range("AI2").Formula = "=" & Range("AI2").Value
Range("AI2").AutoFill Destination:=Range("AI2:AI" & MaxRows), Type:=xlFillDefault
Range("AI:AI") = Range("AI:AI").Value
ws.Range("AJ2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AJ2").Formula
ws.Range("AJ2").Formula = "=" & Range("AJ2").Value
Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & MaxRows), Type:=xlFillDefault
Range("AJ:AJ") = Range("AJ:AJ").Value
ws.Range("AK2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AK2").Formula
ws.Range("AK2").Formula = "=" & Range("AK2").Value
Range("AK2").AutoFill Destination:=Range("AK2:AK" & MaxRows), Type:=xlFillDefault
Range("AK:AK") = Range("AK:AK").Value
ws.Range("AL2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AL2").Formula
ws.Range("AL2").Formula = "=" & Range("AL2").Value
Range("AL2").AutoFill Destination:=Range("AL2:AL" & MaxRows), Type:=xlFillDefault
Range("AL:AL") = Range("AL:AL").Value
ws.Range("AM2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AM2").Formula
ws.Range("AM2").Formula = "=" & Range("AM2").Value
Range("AM2").AutoFill Destination:=Range("AM2:AM" & MaxRows), Type:=xlFillDefault
Range("AM:AM") = Range("AM:AM").Value
ws.Range("AN2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AN2").Formula
ws.Range("AN2").Formula = "=" & Range("AN2").Value
Range("AN2").AutoFill Destination:=Range("AN2:AN" & MaxRows), Type:=xlFillDefault
Range("AN:AN") = Range("AN:AN").Value
ws.Rows("1:1").Value = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Rows("1:1").Value
Workbooks("Cashmacroref.xlsx").Close SaveChanges:=False
With ws.UsedRange
.Columns.AutoFit
.HorizontalAlignment = xlCenter
.Font.Name = "Calibri"
.Font.Size = 10
End With
ws.Rows("1:1").Font.Bold = True
Application.ScreenUpdating = True
End Sub