VBA 表操作1

VBA 新建表、批量建表

例1 创建一个工作簿

注意 .name 与 .range

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub 创建一个工作簿()
'
    Dim wb As Workbook, sht As Worksheet 
	'定义一个workbook对象和一个worksheet对象
    Set wb = Workbooks.Add  '新建一个工作簿
    
    Set sht = wb.Worksheets(1)  'sht 指向wb中的worksheet（1）
	
    With sht
        .Name = "测试"          '修改第一张工作表sheet1的标签名称
        .Range("A1:D1") = Array("A", "B", "C", "D")
		                        ' 为sht 写入head
    End With
    wb.SaveAs ThisWorkbook.Path & "\测试.xlsx" '保存新建表sht到工作簿"测试"
    ActiveWorkbook.Close                       '关闭新建的工作簿
  
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

结果如图

例2 for循环 完成一簿三表

for ... next 语句：
    For <循环体>=<初值> TO <终值> [step 步长值]
        <循环体>
        [Exit For]
        [循环体]
    next [循环变量](可省略）

Sub 一簿三表()

Dim wb As Workbook, i As Integer
i = 1

Set wb = Workbooks.Add
    For i = 1 To 3
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    i = i + 1
    Next
    wb.SaveAs ThisWorkbook.Path & "\一簿三表.xlsx"
    ActiveWorkbook.Close (True)
    MsgBox ("well done!")
End Sub

结果如图

例3 内容转标签

在当前目录下新建表test1.xlsx,sheet1 第一列写入 班级，A1，A2 ,A3 ,A4
如图

 Sub 批量新建工作表()
'根据表A列新建不同的工作表
    Dim i As Integer, wb As String, sht As Worksheet
    i = 2
    wb = ThisWorkbook.Path & "\test1.xlsx"  '指定要打开的文件
    Workbooks.Open (wb)
    Set sht = ActiveWorkbook.Worksheets(1)

    Do While sht.Cells(i, "A") <> ""      '定义循环
        Worksheets.Add after:=Worksheets(Worksheets.Count) '在所有表后插入新的工作表
        ActiveSheet.Name = sht.Cells(i, "A").Value
        ActiveSheet.Range("A1:D1") = Array("A", "B", "C", "D")
         i = i + 1
     
    Loop
    
    ActiveWorkbook.Close True  'True 表示保存更改
    MsgBox ("well done!")

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

结果如图

参考：别怕！Excel VBA 其实很简单