|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 生成班级的表()
'
x2 = 2
Do While Not (IsEmpty(Sheet2.Cells(x2, 1).Value))
njm = Sheet2.Cells(x2, 1).Value
bjm = Sheet2.Cells(x2, 2).Value
bmc = Sheet2.Cells(x2, 1).Value & "年级" & Sheet2.Cells(x2, 2).Value & "班"
bzr = Sheet2.Cells(x2, 3).Value
For i = 1 To Worksheets.Count ' 按工作表数量循环
If Worksheets(i).Name = bmc Then ' 通过循环判断,来完成指定表名是否存在的查找
Application.DisplayAlerts = False ' 此句的作用:删除表时不出现“是否删除的对话框”,相当于VF中设置了 SET SAFETY OFF
Worksheets(i).Delete ' 删除表
'foun = True ' 找到了变量为真,并退出查找
Exit For
End If
Next i
Sheets.Add.Name = bmc ' 增加表,并起名为 BMC 变量中的值
Sheets(bmc).Move after:=Sheets(Sheets.Count) ' 将刚新增的表移动到最右侧
'---先准备好空表,以备粘贴用
Sheet3.Select
Cells.Copy
Sheets(bmc).Select
ActiveSheet.Paste ' 完成空的模板表准备
Sheets(bmc).Cells(1, 1).Value = "某某学校 " & njm & " 年级 " & bjm & " 班学生体温监测表"
Sheets(bmc).Cells(2, 3).Value = Date
Sheets(bmc).Cells(2, 9).Value = bzr
Sheets(bmc).Cells(33, 8).Value = bzr
i = 1
x1 = 2
Do While Not (IsEmpty(Sheet1.Cells(x1, 1).Value))
If Sheet1.Cells(x1, 1).Value = njm And Sheet1.Cells(x1, 2).Value = bjm Then
If i < 31 Then
Sheets(bmc).Cells(i + 3, 2).Value = Sheet1.Cells(x1, 4).Value
i = i + 1
Else
Sheets(bmc).Cells(i - 27, 8).Value = Sheet1.Cells(x1, 4).Value
i = i + 1
End If
End If
x1 = x1 + 1
Loop
x2 = x2 + 1
Loop
Application.DisplayAlerts = True
End Sub |
|