近期因工作之需需对Excel数据进行录入处理加工遂开始研究Excel的VBA编程以提高工作效率,将重复性的工作程序化。
需求① 在一个工作簿(workbook)中根据已知的工作表名称创建多个工作表并分别命名,可以创建同时当然需要研究增删改工作表。
思路:研究vba的基础语法,知道了workbooks(工作簿集)、workbook(工作簿)、worksheet(工作表)两个对象包含多种方法可分别对工作簿和工作表进行操作
主要代码如下:
'批量创建工作表
Sub CreateSheet_Click()
Dim int_rows As Integer, rng As Range, rng1 As Range, int_rows1 As Integer
int_rows = 0
For Each rng In ThisWorkbook.Worksheets(1).UsedRange.Columns(1).Rows
If rng.row > 1 Then
int_rows1 = 0
int_rows = int_rows + 1
ThisWorkbook.Worksheets.Add Count:=1, after:=Sheets(int_rows)
ThisWorkbook.Worksheets(int_rows + 1).Name = rng.Value
For Each rng1 In ThisWorkbook.Worksheets(1).UsedRange.Columns(2).Rows
If rng1.row > 1 Then
int_rows1 = int_rows1 + 1
ThisWorkbook.Worksheets(rng.Value).Cells(1, int_rows1).Value = rng1.Value
End If
Next
End If
Next
MsgBox int_rows & "个工作表已创建完成"
End Sub
'批量删除工作表并不弹出警告框
Sub deleteSheet_Click()
Dim int_rows As Integer, rng As Range
int_rows = 0
Application.DisplayAlerts = False
For Each rng In ThisWorkbook.Worksheets(1).UsedRange.Columns(1).Rows
'int_rows = int_rows + 1
If WorksheetExists(ThisWorkbook, rng.Value) Then
ThisWorkbook.Worksheets(rng.Value).Delete
int_rows = int_rows + 1
End If
Next
Application.DisplayAlerts = True
MsgBox "共删除" & int_rows & "个工作表"
End Sub