VBA-操作工作簿

对for循环再次升级，使用for each：

Sub shishi()
Dim i As Integer
For i = 1 To 10
　　Range("a" & i) = i
Next
End Sub

　　同等效果for each 的做法，此处这个for each 针对的是单元格，也可以是表等 

Sub shishi1()
Dim ge As Range   # rng
Dim i As Integer
For Each ge In Range("a1:a10")
　　i = i + 1
　　ge = i
Next
End Sub

　　删除工作表：除了表名是 绝不能删 外 其余表删除

Sub sc()
Dim biao As Worksheet #sht
Excel.Application.DisplayAlerts = False
For Each biao In Sheets
    If biao.Name <> "绝不能删" Then
        biao.Delete
    End If
Next
Excel.Application.DisplayAlerts = True
End Sub

文件的打开、输入、保存、关闭

Sub wj()
Application.ScreenUpdating = False #锁定屏幕，关闭闪烁
Application.DisplayAlerts = False #关闭告警框
Workbooks.Open Filename:="d:\data\1.xlsx" #打开文件
ActiveWorkbook.Sheets(1).Range("a1") = "到此一游" #输入文字
ActiveWorkbook.Save #保存文件
ActiveWorkbook.Close #关闭文件
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

　　新建以及另存为 add  saveas

Sub chuangjian()
Workbooks.Add
ActiveWorkbook.Sheets(1).Range("a1") = "哈哈，爽"
ActiveWorkbook.SaveAs Filename:="d:\data\222.xlsx"
ActiveWorkbook.Close
End Sub

　　小工具：拆分多个工作表为工作簿 并保存至d盘。

Sub cf()
Dim sht As Worksheet
For Each sht In Sheets
    sht.Copy
    ActiveWorkbook.SaveAs Filename:="d:\data\" & sht.Name & ".xlsx"
    ActiveWorkbook.Close
Next
End Sub

　　一个小陷阱，就是在点击表中的单元格的时候 要先点击单元格,如下面 这样才合理

sheets(1).select    

sheets(1).range("a1").select