VBA_操作表

Sub biao01()
‘结合sheets属性在特定表插入表并且命名
Sheet1.Name = "01biao" ‘重新给表定义属性
Range("A1") = Sheet1.Name  
Sheets(1).Range("B1") = 1
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "02biao"
Dim i As Integer
For i = 3 To 6
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = i & "biao"
Next
End Sub

'选中单元格
Sub danyuange()
    '变量声明
    Dim i As Integer
    '循环
    For i = 1 To 20
    '单元格函数,单元格赋值
    Range("A" & i) = i + 1
    Next
End Sub

'选中表
Sub biao()
Sheets(2).Select '选中表
    '变量声明
    Dim i As Integer
    '循环
    For i = 1 To 20
    '单元格函数,单元格赋值
    Range("A" & i) = i
    Next
End Sub
'插入表
Sub charubiao()
    'Sheets.Add before:=Sheets(1)    '在表1之前插入表
    'Sheets.Add After:=Sheets("单")  '在表1之后插入表
     Dim i As Integer                    '变量声明
     For i = 1 To 10                     '循环
     Sheets(3).Range("A" & i) = i        '特定表的单元格赋值
    Next
    Sheets(1).Range("B1") = Sheets.Count 'sheets.count统计表的数量
    Sheets.Add after:=Sheets(Sheets.Count) '在最后一张表插入表
End Sub

’删除表
Sub shanchubiao()
Excel.Application.DisplayAlerts = False ‘取消警告
Dim i As Integer
For i = 1 To 4
Sheets(1).Delete
Next
Excel.Application.DisplayAlerts = True ’还原警告
End Sub

‘生成日报表
Sub ribaobiao()
Dim i As Integer
For i = 1 To 31
Sheet1.Copy after:=Sheets(Sheets.Count)’复制插入报表
Sheets(Sheets.Count).Name = "5月 " & i & “日”’表命名
Sheets(Sheets.Count).Range("E5") = "2020-5-" & I’单元格输值
Next
End Sub

‘多表汇总
Sub huizongbiao()
Dim i As Integer
For i = 2 To Sheets.Count
Sheet1.Range("B" & i + 8) = Sheets(i).Range("E5")
Sheet1.Range("C" & i + 8) = Sheets(i).Range("E6")
Sheet1.Range("D" & i + 8) = Sheets(i).Range("E44")
Next
End Sub

‘根据条件删除某些表
Sub shanchubiao()
Dim sht As Worksheet  ‘定义一个为表的变量类型
Excel.Application.DisplayAlerts = fals ‘关掉警告
For Each sht In Sheets  ‘在表的集合中遍历表
If sht.Name <> "绝不能删" Then  ‘根据表名进行判断是否删除
sht.Delete
End If
Next
Excel.Application.DisplayAlerts = ture    ‘打开警告
End Sub
‘删除空白单元格
Sub shandanyuange()
Dim i As Integer ‘定义变量
For i = 10 To 1 Step -1 ‘从10开始遍历，每遍历一次 -1
If Range("A" & i) = "" Then ‘判断是否为空
Range("A" & i).Select        ‘如果为空选中单元格
Range("A" & i).Delete        ‘删除单元格
End If
Next
End Sub