表操作2

For each

重复输入10个1
Dim ge As Range
For Each ge In Range("a1:a10")
ge = 1
Next

依次输入1到10
Dim ge As Range
Dim i As Integer
For Each ge In Range("a1:a10")
i = i + 1
ge = i
Next

删除指定表

删除除该表以外的表

'定义表
Dim sht As Worksheet
Excel.Application.DisplayAlerts = False
For Each sht In Sheets
If sht.Name <> "绝不能删" Then
sht.Delete
End If
Next

foreach取表名

操作工作簿

打开某张表
workbooks.Open filename :="...."
向表中写入内容
ActiveWorkbook.Sheets(1).Range("a1") = "...."
意思是在当前打开的工作簿的表1的a1单元格输入...
保存
ActiveWorkbook.Save
关闭
ActiveWorkbook.Close

#打开一张表写入内容并关闭
# 第二行是关闭屏幕更新，记得最后要开启
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open FileName := "文件名"
ActiveWorkbook.Sheets(1).Range("a1") = "hsh"
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True

新建一个工作簿

'新建一个工作簿
Workbooks.Add
ActiveWorkbook.Sheets(1).Range("a1") = "测试文件"
ActiveWorkbook.SaveAs FileName:="路径"
'最后需要关闭文件
ActiveWorkbook.Close

将工作簿中的每个表单独拆分成文件

Dim sht As Worksheet
For Each sht In Sheets
   '当前页的表复制后会自动创建一张新表
   sht.Copy
   '另存自动创建的新表并为用当前页的名字命名
   ActiveWorkbook.SaveAs FileName:="/Users/liupinan/Desktop/temp/" & sht.Name & ".xlsx"
   ActiveWorkbook.Close
Next

结果

书写路径注意空格