培训辅助系统

【实例截图】

【核心代码】

Sub 一键生成附表()
Dim h As Integer
Dim z As Integer
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Set rng1 = Sheet9.Range("A3: K100")
Set rng2 = Sheet5.Range("A5: H40")
Set rng3 = Sheet7.Range("A4: M22")
Set rng4 = Sheet3.Range("A5: F39")
Set rng5 = Sheet15.Range("A8: E15")
Set rng6 = Sheet4.Range("A2: K100")
rng1.ClearContents
rng2.ClearContents
rng3.ClearContents
rng4.ClearContents
rng5.ClearContents
rng6.ClearContents
Dim str9 As String
Dim str8 As String

'这个是只shu清除内容
'第一个名字所在行
h = Sheet10.开始行.Value
'一共多少人

i = Sheet10.人数.Value
riqi = Sheet10.预约时间.Value
a = 3
b = 4
C = 1
D = 5
e = 8
w = 2



For j = 1 To i
'j是第几个数
Sheet9.Cells(a, 1).Value = "众一伍德工程有限公司-乙烯项目-徐善文"
Sheet9.Cells(a, 2) = Sheet1.Cells(h, 4)
Sheet9.Cells(a, 3) = Sheet1.Cells(h, 8)
Sheet9.Cells(a, 4) = Sheet1.Cells(h, 6)
Sheet9.Cells(a, 5) = Sheet1.Cells(h, 10)
str9 = Sheet1.Cells(h, 11)
str8 = Right(str9, 1)

'普工、管理岗、普通司机、电工、焊工、架子工、吊车司机、起重指挥、叉车司机
If Sheet1.Cells(h, 11).Text = "普工" Or Sheet1.Cells(h, 11) = "普通司机" Or Sheet1.Cells(h, 11) = "电工" Or Sheet1.Cells(h, 11) = "焊工" Or Sheet1.Cells(h, 11) = "架子工" Or Sheet1.Cells(h, 11) = "普工" Or Sheet1.Cells(h, 11) = "吊车司机" Or Sheet1.Cells(h, 11) = "叉车司机" Or Sheet1.Cells(h, 11) = "起重指挥" Then
Sheet9.Cells(a, 6) = Sheet1.Cells(h, 11)
Else
If str8 = "工" Or str8 = "机" Or str8 = "挥" Then
Sheet9.Cells(a, 6) = "普工"
Else
Sheet9.Cells(a, 6) = "管理岗"
End If
End If
Sheet9.Cells(a, 7) = "Wood6666"

'承包商导入表格

Sheet5.Cells(D, 1) = Sheet1.Cells(h, 1)
Sheet5.Cells(D, 2) = Sheet1.Cells(h, 4)
Sheet5.Cells(D, 3) = Sheet1.Cells(h, 8)
Sheet5.Cells(D, 4) = Sheet1.Cells(h, 6)
Sheet5.Cells(D, 5) = Sheet1.Cells(h, 10)
Sheet5.Cells(D, 6) = Sheet1.Cells(h, 25)
Sheet5.Cells(D, 7) = Sheet1.Cells(h, 26)
'普工、管理岗、普通司机、电工、焊工、架子工、吊车司机、起重指挥、叉车司机
If Sheet1.Cells(h, 11).Text = "普工" Or Sheet1.Cells(h, 11) = "普通司机" Or Sheet1.Cells(h, 11) = "电工" Or Sheet1.Cells(h, 11) = "焊工" Or Sheet1.Cells(h, 11) = "架子工" Or Sheet1.Cells(h, 11) = "普工" Or Sheet1.Cells(h, 11) = "吊车司机" Or Sheet1.Cells(h, 11) = "叉车司机" Or Sheet1.Cells(h, 11) = "起重指挥" Then
Sheet5.Cells(D, 8) = Sheet1.Cells(h, 11)
Else
If str8 = "工" Or str8 = "机" Or str8 = "挥" Then
Sheet5.Cells(D, 8) = "普工"
Else
Sheet5.Cells(D, 8) = "管理岗"
End If
End If

'派出所筛查表格

Sheet7.Cells(b, 1) = C
Sheet7.Cells(b, 2) = Sheet1.Cells(h, 1)
Sheet7.Cells(b, 3) = Sheet1.Cells(h, 4)
Sheet7.Cells(b, 5) = Sheet1.Cells(h, 23)
Sheet7.Cells(b, 6) = "否"
Sheet7.Cells(b, 7) = Sheet1.Cells(h, 10)
Sheet7.Cells(b, 8) = "是"
Sheet7.Cells(b, 9) = "无需隔离"
Sheet7.Cells(b, 10) = riqi
Sheet7.Cells(b, 11) = Sheet1.Cells(h, 6)
Sheet7.Cells(b, 12) = "绿色"
Sheet7.Cells(b, 13) = Sheet1.Cells(h, 23) & "海口" & "洋浦"
Sheet7.Cells(b, 4) = Sheet1.Cells(h, 24)
'来岛人员申报表格



'入琼导入表格
Sheet4.Cells(w, 1) = Sheet1.Cells(h, 1)
Sheet4.Cells(w, 2) = Sheet1.Cells(h, 4)
Sheet4.Cells(w, 3) = Sheet1.Cells(h, 24)
Sheet4.Cells(w, 4) = Sheet1.Cells(h, 23)
Sheet4.Cells(w, 5) = "否"
Sheet4.Cells(w, 6) = Sheet1.Cells(h, 10)
Sheet4.Cells(w, 7) = "是"
Sheet4.Cells(w, 9) = riqi

Sheet3.Cells(D, 1) = C
Sheet3.Cells(D, 2) = Sheet1.Cells(h, 1)
Sheet3.Cells(D, 3) = Sheet1.Cells(h, 4)
Sheet3.Cells(D, 4) = Sheet1.Cells(h, 8)
Sheet3.Cells(D, 5) = Sheet1.Cells(h, 6)
Sheet3.Cells(D, 6) = Sheet1.Cells(h, 11)
'健康证明表格
Sheet15.Cells(5, 3) = i
Sheet15.Cells(2, 2) = "众一伍德工程有限公司-乙烯项目-徐善文"
f = j Mod 5
'f为余数，即放在这行的第几个格子
k = Int(j / 5)
'k为商，商 8即是放在哪行
z = k 8
If f <> 0 Then
Sheet15.Cells(z, f) = Sheet1.Cells(h, 4)
Else: Sheet15.Cells(z - 1, f 5) = Sheet1.Cells(h, 4)
End If

'培训申请表格
a = a 1
b = b 1
h = h 1
C = C 1
D = D 1
w = w 1
Next
Sheet9.Range("A3:G30").Font.Name = "宋体"
Sheet9.Range("A3:X30").Font.Size = 11
Sheet5.Range("A5:H23").Font.Name = "宋体"
Sheet5.Range("A5:H23").Font.Size = 11
Sheet3.Range("A5:E23").Font.Name = "宋体"
Sheet3.Range("A5:E23").Font.Size = 11
Sheet7.Range("A4:M22").Font.Name = "宋体"
Sheet7.Range("A4:M22").Font.Size = 10
MsgBox "生成成功"
End Sub