4、表格的相关操作

1、获取表格的名称、路径、带路径名称

Sub Wbmsg()

Range("a1") = ThisWorkbook.Name
Range("a2") = ThisWorkbook.Path
Range("a3") = ThisWorkbook.FullName
Sheet1.Range("a4") = 500

End Sub

Sub showsthcode()
MsgBox ActiveSheet.CodeName
End Sub

2、在特定位置增加表格并命名

Sub addsht()
'Sheets.Add before:=Sheets(Sheets.Count)
'Sheets(Sheets.Count).Name = "新增表格"
Sheets.Add before:=Sheet41, Count:=5
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Name = i & "月份"
Next
End Sub

 

3、删除制定表格以外的全部表格
Sub deletesht()

Dim sht As Worksheet
Application.DisplayAlerts = False
For Each sht In Sheets
If sht.Name <> "1月份" Then
sht.Delete
End If
Next
Application.DisplayAlerts = True
End Sub

4、一条代码新增表格并命名

Sub namesht()
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "money"
End Sub

5、隐藏表格等

Sub aa()
Sheets("6月份").Move before:=Sheets("1月份")
Sheets("3月份").Visible = False
Sheets("5月份").Visible = xlSheetHidden
Sheets("4月份").Visible = 0
End Sub

6、输出表格的个数

Sub bb()
Dim i As Integer
i = Sheets.Count
MsgBox "整个工作簿有" & i & "张表格"
End Sub

7、特定位置统一输入特定值

Sub bbc()
Dim n As String
Sheet41.Range("a1:a20").Value = 500
n = "b1:b20"
Sheet47.Range(n) = 1000
End Sub

8、表格内不连续区域改变颜色

Sub changecolor()

Sheet47.Range("b2:b5,b7:b10,b15:b17").Interior.Color = vbGreen

End Sub

9、

Sub 选定特定交叉区域()
Sheet48.Range("b6:b10 a2:d8").Select
End Sub