EXCEL 宏命令大全 ?
打开全部隐藏工作表
Sub 打开全部隐藏工作表()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Visible = True
Next i
End Sub
循环宏
Sub 循环()
AAA = Range(“C2″)
Dim i As Long
Dim times As Long
times = AAA
‘times代表循环次数，执行前把times赋值即可(不可小于1，不可大于2147483647)
For i = 1 To times
Call 过滤一行
If Range(“完成标志”) = “完成” Then Exit For ‘如果名为’完成标志’的命名单元的值等于’完成’，则退出循环，如果一开始就等于’完成’，则只执行一次循环就退出
‘If Sheets(“传送参数”).Range(“A” & i).Text = “完成” Then Exit For ’如果某列出现”完成”内容则退出循环
Next i
End Sub
录制宏时调用“停止录制”工具栏
Sub 录制宏时调用停止录制工具栏()
Application.CommandBars(“Stop Recording”).Visible = True括号里最大能填几
End Sub
高级筛选5列不重复数据至指定表
Sub 高级筛选5列不重复数据至Sheet2()
Sheets(“Sheet2″).Range(“A1:E65536″) = “” ‘清除Sheet2的A:D列
Range(“A1:E65536″).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range( _
“A1″), Unique:=True
Sheet2.Columns(“A:E”).Sort Key1:=Sheet2.Range(“A2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCa:=Fal, Orientation:=xlTopToBottom, SortMethod _
:
=xlPinYin
End Sub
双击单元执行宏(工作表代码)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Range(“$A$1″) = “关闭” Then Exit Sub
Select Ca Target.Address
Ca “$A$4″
Call 宏1
Cancel = True
Ca “$B$4″
Call 宏2
Cancel = True
Ca “$C$4″
Call 宏3
Cancel = True
Ca “$E$4″
Call 宏4
Cancel = True
End Select
End Sub
双击指定区域单元执行宏(工作表代码)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Range(“$A$1″) = “关闭” Then Exit Sub
If Not Application.Interct(Target, Range(“A4:A9″, “C4:C9″)) Is Nothing Then Call 打开隐藏表
End Sub
进入单元执行宏(工作表代码)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘以单元格进入代替按钮对象调用宏
If Range(“$A$1″) = “关闭” Then Exit Sub
Select Ca Target.Address
Ca “$A$5″ ‘单元地址(Target.Address)，或命名单元名字(Target.Name)
Call 宏1背甲龙
Ca “$B$5″
Call 宏2
Ca “$C$5″
Call 宏3
End Select
End Sub
进入指定区域单元执行宏(工作表代码)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(“$A$1″) = “关闭” Then Exit Sub
If Not Application.Inter
ct(Target, Range(“A4:A9″,”C4:C9″)) Is Nothing Then Call 打开隐藏表
End Sub
在多个宏中依次循环执行一个（控件按钮代码）
Private Sub CommandButton1_Click()
Static RunMacro As Integer
Select Ca RunMacro
Ca 0
宏1
RunMacro = 1
Ca 1
宏2
RunMacro = 2
Ca 2
宏3
RunMacro = 0
End Select
End Sub
在两个宏中依次循环执行一个并相应修改按钮名称（控件按钮代码）
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = “保护工作表” Then
Call 保护工作表
.Caption = “取消工作表保护”
Exit Sub
End If
If .Caption = “取消工作表保护” Then
Call 取消工作表保护
.Caption = “保护工作表”
Exit Sub
End If
End With
获麟
End Sub
在三个宏中依次循环执行一个并相应修改按钮名称（控件按钮代码）
Option Explicit
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = “宏1″ Then
Call 宏1
.Caption = “宏2″
Exit Sub
End If
If .Caption = “宏2″ Then
Call 宏2
.Caption = “宏3″
Exit Sub
End If
If .Caption = “宏3″ Then
Call 宏3
.Caption = “宏1″
Exit Sub
End If
End With
End Sub
根据A1单元文本隐藏/显示按钮（控件按钮代码）
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(“A1″) > 2 Then
西安温泉哪里好CommandButton1.Visible = 1
El
CommandButton1.Visible = 0
End If
End Sub
Private Sub CommandButton1_Click()
重排窗口
End Sub
当前单元返回按钮名称（控件按钮代码）
Private Sub CommandButton1_Click()
ActiveCell = CommandButton1.Caption
End Sub
当前单元内容返回到按钮名称（控件按钮代码）
Private Sub CommandButton1_Click()
CommandButton1.Caption = ActiveCell
End Sub
奇偶页分别打印
Sub 奇偶页分别打印()
Dim i%, Ps%
Ps = ExecuteExcel4Macro(“GET.DOCUMENT(50)”) ‘总页数从零开始学英语
MsgBox “现在打印奇数页，按确定开始.”金色的鱼钩教学设计
For i = 1 To Ps Step 2
ActiveSheet.PrintOut from:=i, To:=i
Next i
MsgBox “现在打印偶数页，按确定开始.”
For i = 2 To Ps Step 2
ActiveSheet.PrintOut from:=i, To:=i
Next i
End Sub
自动打印多工作表第一页
Sub 自动打印多工作表第一页()
Dim sh As Integer
Dim x
Dim y
Dim sy
Dim syz
x = InputBox(“请输入起始工作表名字:”)
sy = InputBox(“请输入结束工作表名字:”)
y = Sheets(x).Index
syz = Sheets(sy).Index
For sh = y To syz
Sheets(sh).Select
Sheets(sh).PrintOut from:=1, To:=1
Next sh
End Sub
查找A列文本循环插入分页符
Sub 循环插入分页符()
‘ Selection = Workbooks(“临时表”).Sheets(“表2
″).Range(“A1″) 调用指定地址内容
Dim i As Long
Dim times As Long
times = Application.WorksheetFunction.CountIf(Sheet1.Range(“a:a”), “分页”)
‘times代表循环次数，执行前把times赋值即可(不可小于1，不可大于2147483647)
For i = 1 To times
Call 插入分页符
Next i
End Sub
Sub 插入分页符()
Cells.Find(What:=”分页”, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCa:=Fal) _
.Activate
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End Sub
Sub 取消原分页()
Cells.Select
ActiveSheet.RetAllPageBreaks
End Sub
将A列最后数据行以上的所有B列图片大小调整为所在单元大小
Sub 将A列最后数据行以上的所有B列图片大小调整为所在单元大小()
Dim Pic As Picture, i&
i = [A65536].End(xlUp).Row
For Each Pic In Sheet1.Pictures
If Not Application.Interct(Pic.TopLeftCell, Range(“B1:B” & i)) Is Nothing Then
Pic.Top = Pic.TopLeftCell.Top
Pic.Left = Pic.TopLeftCell.Left
Pic.Height = Pic.TopLeftCell.Height
Pic.Width = Pic.TopLeftCell.Width
End If
Next
End Sub
返回光标所在行数
Sub 返回光标所在行数()
x = ActiveCell.Row
Range(“A1″) = x
End Sub
在A1返回当前选中单元格数量
Sub 在A1返回当前选中单元格数量()
[A1] = Selection.Count
End Sub
返回当前工作簿中工作表数量
Sub 返回当前工作簿中工作表数量()
t = Application.Sheets.Count
MsgBox t
End Sub
返回光标选择区域的行数和列数
Sub 返回光标选择区域的行数和列数()
x = Selection.Rows.Count
y = Selection.Columns.Count
Range(“A1″) = x
Range(“A2″) = y
End Sub
工作表中包含数据的最大行数
Sub 包含数据的最大行数()
n = Cells.Find(“*”, , , , 1, 2).Row
MsgBox n
End Sub
返回A列数据的最大行数
Sub 返回A列数据的最大行数()
n = Range(“a65536″).End(xlUp).Row
Range(“B1″) = n
End Sub
将所选区域文本插入新建文本框
Sub 将所选区域文本插入新建文本框()
For Each rag In Selection
n = n & rag.Value & Chr(10)
Next
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, ActiveCell.Left + ActiveCell.Width, ActiveCell.Top + ActiveCell.Height, 250#, 100).Select
Selection.Characters.Text = “问题：” & n
With Selection.Characters(Start:=1, Length:=3).Font
.Name = “黑体”
.FontStyle = “常规”
.Size = 12
End With
End Sub
批量插入地址批注
Sub 批量插入地址批注()
On Error Resume Next
Dim r As Range
If Selection.Cells.Count > 0 Then
For Each r In Selection
r.Comment.Delete
r.
AddComment
r.Comment.Visible = Fal
r.Comment.Text Text:=”本单元格：” & r.Address & ” of ” & Selection.Address
Next
End If
End Sub
批量插入统一批注
Sub 批量插入统一批注()
Dim r As Range, msg As String
msg = InputBox(“请输入欲批量插入的批注”, “提示”, “随便输点什么吧”)
If Selection.Cells.Count > 0 Then
For Each r In Selection
r.AddComment
r.Comment.Visible = Fal
r.Comment.Text Text:=msg
Next
End If
End Sub
以A1单元内容批量插入批注
Sub 以A1单元内容批量插入批注()
Dim r As Range
If Selection.Cells.Count > 0 Then
For Each r In Selection
r.AddComment
r.Comment.Visible = Fal
r.Comment.Text Text:=[a1].Text
Next
End If
End Sub
不连续区域插入当前文件名和表名及地址
Sub 批量插入当前文件名和表名及地址()
For Each mycell In Selection
mycell.FormulaR1C1 = “[" + ActiveWorkbook.Name + "]” + ActiveSheet.Name + “!” + mycell.Address
Next
End Sub
不连续区域录入当前单元地址
Sub 区域录入当前单元地址()
For Each mycell In Selection
mycell.FormulaR1C1 = mycell.Address
Next
End Sub
连续区域录入当前单元地址
Sub 连续区域录入当前单元地址()
Selection = “=ADDRESS(ROW(),COLUMN(),4,1)”
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=Fal, Transpo:=Fal
End Sub
返回当前单元地址
不用文字的书和信Sub 返回当前单元地址()
d = ActiveCell.Address
[A1] = d
End Sub
不连续区域录入当前日期
Sub 区域录入当前日期()
Selection.FormulaR1C1 = Format(Now(), “yyyy-m-d”)
End Sub
不连续区域录入当前数字日期
Sub 区域录入当前数字日期()
Selection.FormulaR1C1 = Format(Now(), “yyyymmdd”)
End Sub
不连续区域录入当前日期和时间
Sub 区域录入当前日期和时间()
Selection.FormulaR1C1 = Format(Now(), “yyyy-m-d h:mm:ss”)
End Sub
不连续区域录入对勾
Sub 批量录入对勾()
Selection.FormulaR1C1 = “√”
End Sub
不连续区域录入当前文件名
Sub 批量录入当前文件名()
Selection.FormulaR1C1 = ThisWorkbook.Name
End Sub
不连续区域添加文本
Sub 批量添加文本()
Dim s As Range
For Each s In Selection
s = s & “文本内容”
Next
End Sub
不连续区域插入文本
Sub 批量插入文本()
Dim s As Range
For Each s In Selection
s = “文本内容” & s
Next
End Sub
从指定位置向下同时录入多单元指定内容
Sub 从指定位置向下同时录入多单元指定内容()
Dim arr
arr = Array(“1″, “2″, “13″, “25″, “46″, “12″, “0″, “20″)
[B2].Resize(8, 1) = Application.WorksheetFunction.Transpo(arr)
End Sub
按aa工作表A列的内容排列工作表标签顺序
Sub 按aa工作表A列的内容排列工作表标签顺序()
Dim I%, str1$
I = 1
Sheets(“aa”).Select
Do While Cells(I, 1).Value <> “”
str1 = Trim(Cells(I, 1).Value)
Sheets(str1).Select
Sheets(str1).Move after:=Sheets(I)
I = I + 1
Sheets(“aa”).Select
Loop
End Sub
以A1单元文本作表名插入工作表
Sub 以A1单元文本作表名插入工作表()
Dim nm As String
nm = [a1]
Sheets.Add
ActiveSheet.Name = nm
End Sub
删除全部未选定工作表
Sub 删除全部未选定工作表()
Dim sht As Worksheet, n As Integer, iFlag As Boolean
Dim ShtName() As String
n = ActiveWindow.SelectedSheets.Count
ReDim ShtName(1 To n)
n = 1
For Each sht In ActiveWindow.SelectedSheets
ShtName(n) = sht.Name
n = n + 1
Next
Application.DisplayAlerts = Fal
For Each sht In Sheets
iFlag = Fal
For i = 1 To n – 1
If ShtName(i) = sht.Name Then
iFlag = True
Exit For
End If
Next
If Not iFlag Then sht.Delete
Next
Application.DisplayAlerts = True
End Sub
工作表标签排序
Sub 工作表标签排序()
Dim i As Long, j As Long, nums As Long, msg As Long
msg = MsgBox(“工作表按升序排列请选 ‘是[Y]‘. ” & vbCrLf & vbCrLf & “工作表按降序排列请选 ‘否[N]‘”, vbYesNoCancel, “工作表排序”)
If msg = vbCancel Then Exit Sub
nums = Sheets.Count
If msg = vbYes Then ‘Sort ascending
For i = 1 To nums
For j = i To nums
If UCa(Sheets(j).Name) < UCa(Sheets(i).Name) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
El ‘Sort descending
For i = 1 To nums
For j = i To nums
If UCa(Sheets(j).Name) > UCa(Sheets(i).Name) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
End If
End Sub
定义指定工作表标签颜色
Sub 定义指定工作表标签颜色()
Sheets(“Sheet1″).Tab.ColorIndex = 46
End Sub
在目录表建立本工作簿中各表链接目录
Sub 在目录表建立本工作簿中各表链接目录()
Dim s%, Rng As Range
On Error Resume Next
更少的英文Sheets(“目录”).Activate
If Err = 0 Then
Sheets(“目录”).UdRange.Delete
El
Sheets.Add
ActiveSheet.Name = “目录”
End If
For i = 1 To Sheets.Count
If Sheets(i).Name <> “目录” Then
s = s + 1
Set Rng = Sheets(“目录”).Cells(((s – 1) Mod
