销项发票明细表整理

Sub 销项发票()
Dim i, j, k, m, n
Dim hang1, hang2, hang_count, lie_spmc
Dim arr_neibu, mrr, p
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Dim ws As Worksheet

arr_neibu = Array("深圳市瑞意博科技股份有限公司", _
"深圳市瑞意博医疗设备有限公司", _
"深圳市矽海数据采集系统有限公司", _
"深圳市瑞意博信息技术有限公司", _
"上海瑞意博信息科技有限公司", _
"武汉市瑞意博信息科技有限公司")
For i = 0 To UBound(arr_neibu)
    dic(arr_neibu(i)) = ""
Next i
'lie_spmc = Range("6:6").Find("商品名称").Column
Range("A:A").Find("份数").EntireRow.Delete
hang1 = Range("J6").End(xlDown).Row

'填充空白行
For m = 7 To hang1
    For n = 1 To 7
        If Cells(m, n) = "" Then
            Cells(m, n) = Cells(m - 1, n)
        End If
    Next n
Next m
'删除小计行
For j = hang1 To 7 Step -1
    If Range("J" & j).Value = "小计" Then
        Range("J" & j).EntireRow.Delete
    End If
Next j

Range("A6").End(xlToRight).Offset(0, 1).Value = "含税金额"
Range("A6").End(xlToRight).Offset(0, 1).Value = "客户类别"
hang2 = Range("J6").End(xlDown).Row
For k = 7 To hang2
    If dic.exists(Range("C" & k).Value) Then
        Range("T" & k) = "内部公司"
    Else
        Range("T" & k) = "外部公司"
    End If
Next k

mrr = Range("M6:Q" & hang2)
For p = 1 To UBound(mrr, 1)
    For q = 1 To UBound(mrr, 2)
        mrr(p, q) = mrr(p, q)
    Next q
Next p
Range("M6").Resize(UBound(mrr, 1), UBound(mrr, 2)) = mrr
Range("S7") = "=O7+Q7"
Range("S7").AutoFill Range("S7:S" & hang2), 4
Range("O7:S" & hang2).NumberFormatLocal = "#,##0.00_ "
Range("S6:T" & hang2).Font.Size = 9
Range("A:T").ColumnWidth = 8
Cells(1, 1).Select
Cells(7, 3).Select
ActiveWindow.FreezePanes = True
Range("A6:T" & hang2).AutoFilter
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = Sheets(Sheets.Count)
ws.Name = "TSB"
End Sub