VBA 表操作1

时间：2024-08-13 19:35:38

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

结果如图

VBA 表操作1

例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

结果如图

VBA 表操作1

例3 内容转标签

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

如图

VBA 表操作1

 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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

结果如图

VBA 表操作1

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

相关文章