EXCEL常用宏..docx

EXCEL 259个常用宏宏文件集打开全部隐藏工作表Sub 打开全部隐藏工作表()Dim i As IntegerFor i = 1 To Sheets.Count Sheets(i).Visible = TrueNext iEnd Sub循环宏Sub 循环()AAA = Range(C2) Dim i As Long Dim times As Long times = AAA times代表循环次数，执行前把times赋值即可(不可小于1，不可大于2147483647) For i = 1 To timesCall 过滤一行 If Range(完成标志) = 完成 Then Exit For 如果名为完成标志的命名单元的值等于完成,则退出循环,如果一开始就等于完成,则只执行一次循环就退出 If Sheets(传送参数).Range(A i).Text = 完成 Then Exit For 如果某列出现完成内容则退出循环 Next iEnd Sub录制宏时调用“停止录制”工具栏Sub 录制宏时调用停止录制工具栏()Application.CommandBars(Stop Recording).Visible = TrueEnd 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, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlPinYinEnd Sub双击单元执行宏(工作表代码)Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range($A$1) = 关闭 Then Exit Sub Select Case Target.Address Case $A$4 Call 宏1 Cancel = True Case $B$4 Call 宏2 Cancel = True Case $C$4 Call 宏3 Cancel = True Case $E$4 Call 宏4 Cancel = True End SelectEnd Sub双击指定区域单元执行宏(工作表代码)Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range($A$1) = 关闭 Then Exit Sub If Not Application.Intersect(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 Case Target.Address Case $A$5 单元地址(Target.Address),或命名单元名字(Target.Name) Call 宏1 Case $B$5 Call 宏2 Case $C$5 Call 宏3 End SelectEnd Sub进入指定区域单元执行宏(工作表代码)Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range($A$1) = 关闭 Then Exit Sub If Not Application.Intersect(Target, Range(A4:A9,C4:C9)) Is Nothing Then Call 打开隐藏表End Sub在多个宏中依次循环执行一个（控件按钮代码）Private Sub CommandButton1_Click()Static RunMacro As IntegerSelect Case RunM