VBA：零件成本统计之二（材料&外协金额表）

第二步，通过已经生成的机加任务，生成汇总表格及材料外协金额表

''生成汇总统计和材料外协金额表
Sub statistical()
Dim WS As Worksheet
Dim rng As Range, rngold As Range
Dim sheetName As String
Dim rowscount As Long, MAXRGN As Long
Dim i As Integer, j As Integer




    Sheets("机加任务及工时").Select
    
    rowscount = ActiveSheet.UsedRange.Rows.count
    
    Set rng = ActiveSheet.Range("A1:J" & rowscount)


    ' 新增加汇总统计表，将任务数据复制过去
    sheetName = "汇总统计"
    '    ' 遍历工作簿中的所有工作表，检查是否存在同名工作表
    For Each WS In ThisWorkbook.Sheets
     If WS.Name = sheetName Then
        i = 1
     End If
    Next
    '如果没有则新增
    If i = 0 Then
      Set WS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
      WS.Name = sheetName
    End If
    '清除原有数据
    ActiveWorkbook.Sheets(sheetName).Select
     MAXRGN = Worksheets(sheetName).Range("a" & Rows.count).End(xlUp).Row
    If MAXRGN <> 0 Then
      Set rngold = ActiveSheet.Range("A1:AZ" & MAXRGN)
      rngold.Borders.LineStyle = xlNone  ' 移除边框
      rngold.Clear ' 清除数据
      
    End If
    

    
 
    Sheets("汇总统计").Range("A1").Select
    rng.Copy Sheets("汇总统计").Range("A1")
    '去除重复数据
    ActiveSheet.Range("A1:J" & rowscount).RemoveDuplicates Columns:=1, Header:=xlYes
    
    rowscount = ActiveSheet.Range("A" & Rows.count).End(xlUp).Row
    '排序
    
    ActiveSheet.Range("A1:J" & rowscount).Sort Key1:=Range("b1"), Header:=xlYes, Order1:=xlAscending
    
'    For i = 1 To rowscount - 1
'       Cells(i + 1, 1) = i
'    Next
    Cells(1, 1) = "内码"
    Cells(1, 2) = "任务编号"
    Cells(1, 3) = "类型"
    Cells(1, 4) = "机床编号"
    Cells(1, 5) = "物料代码"
    Cells(1, 6) = "物料名称"
    Cells(1, 7) = "物料规格"
    Cells(1, 8) = "数量"
    Cells(1, 9) = "下达日期"
    Cells(1, 10) = "入库日期"
    
       
        
'    '冻结第1行
'     ActiveSheet.Rows("1:1").Select
'     ActiveWindow.FreezePanes = True
      moformat.format '格式
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''将任务单号复制出来，拷到材料&外金额表中去

    rowscount = ActiveSheet.UsedRange.Rows.count

    Set rng = ActiveSheet.Range("A1:B" & rowscount)
'

''新增材料&外协金额表
j = 0
sheetName = "材料&外协金额表"
'    ' 遍历工作簿中的所有工作表，检查是否存在同名工作表
For Each WS In ThisWorkbook.Sheets
 If WS.Name = sheetName Then
    j = 1
 End If
Next
'如果没有则新增，放在汇总统计前
If j = 0 Then
  Set WS = ThisWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets("汇总统计"))
  WS.Name = sheetName
End If

    Sheets(sheetName).Select
    
    rng.Copy ActiveSheet.Range("A1")




    Sheets("目录").Select

End Sub