第2章Sheet（工作表）对象代码【超实用VBA】..docVIP

第2章 Sheet（工作表）对象 范例18 引用工作表的方法 18-1 使用工作表名称 Sub ShtName() Worksheets(Sheet2).Range(A1) = Excel 2007 End Sub 18-2 使用工作表索引号 Sub ShtIndex() Worksheets(Worksheets.Count).Select End Sub 18-3 使用工作表代码名称 Sub ShtCodeName() Sheet3.Select End Sub 范例19 选择工作表的方法 Sub ShtSelect() MsgBox 下面将选择 Sheet2.Name 工作表 Sheet2.Select MsgBox 下面将激活 Sheet3.Name 工作表 Sheet3.Activate End Sub Sub SelectSht() Dim Sht As Worksheet For Each Sht In Worksheets Sht.Select False Next End Sub Sub SelectSheets() Worksheets.Select End Sub Sub ArraySheets() Worksheets(Array(1, 3)).Select End Sub 范例20 遍历工作表的方法 20-1 使用For...Next 语句Sub TraversalShtOne() Dim i As Integer Dim Str As String For i = 1 To Worksheets.Count Str = Str Worksheets(i).Name vbCrLf Next MsgBox 工作簿中含有以下工作表： vbCrLf Str End Sub 20-1 使用For Each...Next 语句Sub TraversalShtTwo() Dim Sht As Worksheet Dim Str As String For Each Sht In Worksheets Str = Str Sht.Name vbCrLf Next MsgBox 工作簿中含有以下工作表： vbCrLf Str End Sub 范例21 工作表的添加与删除 Sub ShtAddOne() Worksheets.Add.Name = 数据 End Sub Sub ShtAddTwo() Dim i As Integer Dim Sht As Worksheet With Worksheets For i = 1 To 6 Set Sht = .Add(after:=Worksheets(.Count)) Sht.Name = i Next End With Set Sht = Nothing End Sub Sub ShtDel() Dim Sht As Worksheet Application.DisplayAlerts = False For Each Sht In Worksheets If Sht.Name 工作表的添加与删除 Then Sht.Delete End If Next Application.DisplayAlerts = True Set Sht = Nothing End Sub Sub ShtAddThree() Dim Sht As Worksheet For Each Sht In Worksheets If Sht.Name = 数据 Then If MsgBox(工作簿中已有数据工作表,是否删除后添加?, 36) = 6 Then Application.DisplayAlerts = False Sht.Delete Application.DisplayAlerts = True Else Exit Sub End If