vba移动文件_VBA学习笔记8:工作表操作

学习资源：《Excel VBA从入门到进阶》第10集 by蓝色幻想

一、Excel工作表的分类

Excel工作表有两大类，一类是我们平常用的工作表(worksheet)，另一类是图表、宏表等。这两类的统称是sheets。

在工作表的标签页上右击—选择插入，可以看到所有工作表类型。

• Sheets("A")，名称为A的excel工作表。
• workbooks(2)，按从左到右的排列顺序（包含隐藏工作表），第二个打开的工作簿。
• ActiveSheet ，正在操作的那个就是ActiveSheet。

二、Excel工作表的操作

1. 判断A工作表文件是否存在

• 利用for循环和if语句，查找工作表的名称是否为"A"。
• 查到后弹出窗口显示“A工作表存在”（MsgBox "......"），并退出整段程序停止操作（Exit Sub）；
• 反之，继续查找直至所有工作表名称被比对完都没找到，并弹出窗口显示“A工作表不存在”。

Sub s1()
Dim X As Integer

For X = 1 To Sheets.Count
    If Sheets(X).Name = "A" Then
         MsgBox "A工作表存在"
         Exit Sub
    End If
Next

MsgBox "A工作表不存在"
End Sub

2. 工作表的插入Sheets.Add

Sub 新建工作表()
Dim sh As Worksheet

Set sh = Sheets.Add

sh.Name = "模板"
sh.Range("a1") = 100

End Sub

上面的代码是利用工作表变量给工作表赋值新名字，而下面的代码则是利用ActiveSheet的这一概念来给新工作表重命名。

Sub 新建工作表()

Sheets.Add
ActiveSheet.Name = "模板"
    
End Sub

3. 工作表隐藏和取消隐藏

是否可视是工作表的一种属性，隐藏就是更改这一属性。

Sub s3()

Sheets(2).Visible = False  '隐藏第二个工作表，或可写为Sheets(2).Visible = 0
Sheets(2).Visible = True  '取消隐藏第二个工作表，或可写为Sheets(2).Visible = -1

End Sub

4. 工作表的移动

Sub s4()

Sheets("Sheet2").Move before:=Sheets("sheet1") 'sheet2移动到sheet1前面
Sheets("Sheet1").Move after:=Sheets(Sheets.Count) 'sheet1移动到所有工作表的最后面

End Sub

Sheets.Count 获取本工作簿工作表的数目

5. 工作表的复制

（1）在本工作簿中复制，原表和复制到的表都在同一个工作簿中。

先来一波不用VBA的正常操作：

在“模板”工作表标签页右击，选择“移动或复制”

勾选“建立副本”，把副本放在“1日”工作表前

重命名复制的“模板2”工作表为“测试”。

Sub s5() '在本工作簿中

Sheets("模板").Copy before:=Sheets(1)
ActiveSheet.Name = "1日"
ActiveSheet.Range("a1") = "测试"

End Sub

（2）把复制的工作表另存为新工作簿。

相当于在移动或复制工作表时，选择”将选定工作表移至新工作簿“，然后把新工作簿另存为当前工作簿保存的路径下，并命名为”1日“工作簿。

Sub s6() '另存为新工作簿

Activesheet.copy '复制工作表
ActiveWorkbook.SaveAs ThisWorkbook.Path & "/1日.xls"  '另存为新工作簿
ActiveWorkbook.Close True  '关闭工作簿

End Sub

6. 保护工作表

完整：表达式.Protect(密码, 形状,内容,方案,保护用户界面)

简洁：表达式.Protect"密码"

Sub s7()

Sheets("sheet2").Protect "123"

End Sub

判断工作表是否添加了保护密码：

   Sub s8() 
      If Sheets("sheet2").ProtectContents = True Then
        MsgBox "工作簿保护了"
      Else
        MsgBox "工作簿没有添加保护"
      End If
   End Sub

7. 删除工作表

     Sub s9()
       Application.DisplayAlerts = False
         Sheets("模板").Delete
       Application.DisplayAlerts = True
     End Sub

删除工作表时，EXCEL会弹出警告，可设置关闭该警告。

Application.DisplayAlerts = False

删除完成后记得打开警告，不然以后都不会弹出警告。

8. 工作表的选取

     Sub s10()
       Sheets("sheet2").Select
     End Sub

三、练习

题目１:

复制“日报表模板”工作表(已隐藏)至本工作簿最后一个位置，复制后的工作表名称为最后的日期天数+1&"报表"的格式。点击“生成日报”按钮就生成一个新的日报表。

如：当前情况下，没有任何一天的日报表，则新复制的工作表名称是“1日报表”，如果再点击添加时就是1+1=2日报表。如果目前已存在5天的日报表，则复制后的工作表名称应为“6日报表”

PS：“日报表模板”工作表要用代码取消隐藏，复制后要隐藏起来。

Sub 日报表格式生成()
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = Sheets("日报表模板")
    ws.Visible = True
    '显示"日报表模板"工作表，它是第二个工作表。
    
    i = Sheets.Count
    '数当前工作表的数量。
    ws.Copy after:=Sheets(Sheets.Count)
    '复制"日报表模板"工作表，并放在所有工作表的最后。EXCEL会停留在当前工作簿。
     ActiveSheet.Name = i - 1 & "日报表"
    '重命名当前工作表
    
    ws.Visible = False
    '隐藏"日报表模板"工作表
    Sheets(1).Select
    '回到题目所在工作表
End Sub

题目2:

把所有日报表另存为工作簿到本文夹下，工作簿名称为工作表的名称。

Sub 另存报表()
Dim i As Integer
Dim sh As Worksheet
    
    Application.ScreenUpdating = False
    '取消屏幕更新，加快代码运行速度
    
    For i = 1 To Sheets.Count
        If Sheets(i).Name Like "*日报表" Then
        '如果工作簿名称包含“日报表”，则复制该工作表，并另存为当前工作表到当前工作簿路径下
            Sheets(i).Copy
            Set sh = ActiveSheet
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & sh.Name & ".xls"
            ActiveWorkbook.Close True
        End If
    Next
    
    Application.ScreenUpdating = True
    '恢复屏幕刷新

End Sub

经评论蔡花花同学指出：原来写的sh.SaveAs ThisWorkbook.Path & "" & sh.Name & ".xls"，修改为ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & sh.Name & ".xls"，只有工作簿才能使用saveas.

题目3:

删除所有日报表。

Sub 清除日报表()
Dim i As Integer
Dim sh As Worksheet

    For Each sh In Sheets
        If sh.Name Like "*日报表" Then
        Application.DisplayAlerts = False

        sh.Delete

        Application.DisplayAlerts = True
        End If
    Next

End Sub