VBA代码汇总情况.docx

1：打开所有隐藏工作表 ? Sub?打开所有隐藏工作表() Dim?i?As?Integer For?i?=?1?To?Sheets.Count Sheets(i).Visible?=?True Next?i? End?Sub ?? 2：循环宏 ? 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??? ?????? 3：录制宏时调用“停止录制”工具栏??? Sub?录制宏时调用停止录制工具栏() ??????Application.CommandBars(Stop?Recording).Visible?=?True? End?Sub???????? ? 4：高级筛选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?_ ?????????:=xlPinYin? End?Sub???????? 5：双击单元执行宏(工作表代码)??? 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?Select? End?Sub ? 6：双击指定区域单元执行宏(工作表代码) 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 ? 7：进入单元执行宏(工作表代码)??? 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 ?????