20190321xlVBA_汇总表按模板生成明细表

 

 

 

 

 

 

Public Sub 汇总表转信息表()
    '日期
    '作者  Next
    'QQ   84857038
    Dim Wb, Sht, msht, NewSht, rng
    Set Wb = Application.ThisWorkbook
    Set Sht = Wb.Worksheets("Sheet2")
    Set msht = Wb.Worksheets("Sheet3")
    With Sht
        endrow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
        If endrow <= 1 Then Exit Sub
        Set rng = .Range("A3:O" & endrow)
        arr = rng.Value
    End With
    For i = LBound(arr) To UBound(arr)
        msht.Copy After:=Wb.Worksheets(Wb.Worksheets.Count)
        Set NewSht = Wb.Worksheets(Wb.Worksheets.Count)
        With NewSht
            newname = arr(i, 3) '意思是以第三列的姓名来给新表格命名
            Application.DisplayAlerts = False
            Wb.Worksheets(newname).Delete
            Application.DisplayAlerts = True
            .Name = newname
            .Range("B2").Value = arr(i, 3) '意思是小表B2单元格的内容=大表的第3列的姓名，以此类推
            '以此类推
        End With
    Next i
    Set Wb = Nothing
    Set Sht = Nothing
    Set msht = Nothing
    Set NewSht = Nothing
    Set rng = Nothing
End Sub