excelvba代码应用到所有表格.docx

1、excelvba代码应用到所有表格竭诚为您提供优质文档/双击可除excel,vba代码应用到所有表格篇一：excel常用Vba代码删除b列中字符串数值少于21的单元格所在的行sub删除行()r=Range(b65536).end(xlup).Row行数Forh=rto1step-1ifcells(h,2)nextendsub-【工作表合并】将同一工作簿中的所有工作表合并到一个工作表中新建一个工作表，写入代码在新建的工作表标签处右键查看代码（找不到的直接按一下alt+F11)把下面的代码复制进去然后点上面的运行运行子程序即可：sub合并当前工作簿下的所有工作表()application.scre

2、enupdating=FalseForj=1tosheets.countifsheets(j).nameactivesheet.namethenx=Range(a65536).end(xlup).Row+1sheets(j).usedRange.copycells(x,1)endifnextRange(b1).selectapplication.screenupdating=truemsgbox当前工作簿下的全部工作表已经合并完毕！,vbinformation,提示endsub*代码这样写也行：subc()Fori=sheets.countto2step-1sheets(i).selectsh

3、eets(i).usedRange.copysheets(1).selectcells(cells(65000,1).end(xlup).Row+1,1).selectactivesheet.pastesheets(i).deletenextiendsub*把一个工作簿中的所有表单合并成一个表单，怎么去掉重复的表头、标题行？方法如下：subc()Fori=sheets.countto2step-1sheets(i).usedRange.offset(1).copysheets(1).cells(65536,1).end(xlup).offset(1)nextiendsub说明：函数oFFset

4、(reference,rows,cols,height,width)以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。通俗的讲就是oFFset(参考单元格，移动的行数，移动的列数，所要引用的行数，所要引用的列数）参考关于offset函数第三行中第一个offset(1)是假设要要去掉的表头行数，如果有2行表头，就改成offset(2)，要去掉几行表头括号中的数字就改成几。第二个offset(1)表示合并以后表格与表格之间要间隔的空行，offset(1)表示不留空行，offset(2)表示间隔1行空行，以此类推。也可以这样写：su

5、bc()Fori=sheets.countto2step-1sheets(i).usedRange.offset(2).copysheets(1).cells(cells(65536,1).end(xlup).Row+1,1).offset(0)这个offset(0)可以不要nextiendsub*或者用以下宏代码将同一工作簿中的所有工作表合并到一个新建的工作表中按alt+F11调出窗口，插入一个模块，然后把下面的代码复制进去。subhz()setnewsheet=sheets.add(type:=xlworksheet)生成一个新表sheets(newsheet.index).movebef

6、ore:=sheets(1)将此新表移动到最前面Fori=2toworksheets.countsheets(i).usedRange.copynewsheet.cells(a65536.end(xlup).Row+2,1)将其他表的已使用区域复制到新表中nextimsgbox合并完成endsub这段代码很简单，其中第四行中用FoR循环得到当前工作簿中的所有工作表，第五行中使用usedRange得到每个工作表的“已使用区域”，然后用copy方法把这些“已使用区域”中的内容复制到新建工作表中。语句cells(a65536.end(xlup).Row+2,1)的作用是得到新建工作表的列中的最后空白

7、单元格(即要在哪个位置粘贴)，加2的作用是使每次复制数据间隔2行空格（此处应表示间隔1行空格，加1的话，表示合并的表格与表格之间不留空格）。回到excel窗口，执行“工具-宏-宏”中的“hz”宏就会自动合并工作表了。（经本人测试，不能使用右键点击标签查看代码再粘入代码的方式，应该运用菜单栏插入模块的方式）-【工作簿合并】将需要合并的工作簿文件放置在一个文件夹中，并新建一个工作簿，写入代码：sub合并工作薄()dimFilestoopendimxasintegeronerrorgotoerrhandlerapplication.screenupdating=FalseFilestoopen=ap

8、plication.getopenFilename_(FileFilter:=microsoftexcel文件(*.xls),*.xls,_multiselect:=true,title:=要合并的文件)iftypename(Filestoopen)=booleanthenmsgbox没有选中文件gotoexithandlerendifx=1whilexworkbooks.openFilename:=Filestoopen(x)sheets().moveafter:=thisworkbook.sheets(thisworkbook.sheets.count)x=x+1wendexithandl

9、er:application.screenupdating=trueexitsuberrhandler:msgboxerr.descriptionResumeexithandlerendsub-显示隐藏的工作表subshowallsheets()使当前工作簿中的所有工作表都显示(即将隐藏的工作表也显示)dimwsasworksheetForeachwsinsheetsws.Visible=truenextwsendsub-根据sheet2中的数据，检查sheet1中的重复数据，并且进行后续的操作（将重复数据删除或者拷贝出来）的操作。application.screenupdating=Fals

10、ec=2第一个工作表检测b列x=1第一条检测结果放在第1行count=1First_sheet_row=sheets(1).cells(65536,c).end(xlup).Rowsecond_sheet_row=sheets(2).cells(65536,c).end(xlup).Rowdimto_be_deleted(5369)asstringForj=1to5368to_be_deleted(j)=trim(cstr(sheets(2).cells(j,2).Value)nextjFori=1toFirst_sheet_rowFirst_value=trim(cstr(sheets(1)

11、.cells(i,c).Value)Forj=1to5368msgboxto_be_deleted(j)ifFirst_value=to_be_deleted(j)thensheets(1).Range(asheets(2).cel(excel,vba代码应用到所有表格)ls(j,3).Value=copiedapplication.cutcopymode=Falsesheets(1).Range(asheets(3).pastedestination:=sheets(3).Range(asheets(3).pastecount=count+1i=i-1endifnextjnextiappli

12、cation.screenupdating=truemsgbox共删除了extendedproperties=excel8.0;datasource=andmonth(日期)=groupby区域,存货类sheets(sheet2).a5.copyFromRecordsetconn.execute(sql)conn.close:setconn=nothingendsub-sub查询方法二()setconn=createobject(adodb.connection)conn.opendsn=excelfiles;dbq=andmonth(日期)=groupby区域,存货类sheets(sheet

13、2).a5.copyFromRecordsetconn.execute(sql)conn.close:setconn=nothingendsub*b、根据本工作簿2个表的不同类别查询求和写法范本sub根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()setconn=createobject(adodb.connection)conn.openprovider=microsoft.jet.oledb.4.0;datasource=andmonth(日期)=andmonth(开票日期)=dbq=extendedproperties=excel8.0;hdr=no;datas

14、ource=dbq=声明a为整型变量dima声明a为变体变量dimaasstring声明a为字符串变量dimaascurrency,bascurrency,cascurrency声明a,b,c为货币变量.声明变量可以是：byte、boolean、integer、long、currency、single、double、decimal（当前不支持）、date、string（只限变长字符串）、string*length（定长字符串）、object、Variant、用户定义类型或对象类型。强制声明变量optionexplicit说明：该语句必在任何过程之前出现在模块中。声明常数用来代替文字值。cons

15、t常数的默认状态是private。constmy=456声明public常数。publicconstmystring=help声明privateinteger常数。privateconstmyintasinteger=5在同一行里声明多个常数。constmystr=hello,mydoubleasdouble=3.4567选择当前单元格所在区域在excel97中，有一个十分好的功能，他就是把鼠标放置在一个有效数据单元格中，执行该段代码，你就可以将连在一起的一片数据全部选中。只要将该段代码加入到你的模块中。submy_selectselection.currentRegion.selectend

16、sub返回当前单元格中数据删除前后空格后的值submy_trimmsgboxtrim(activecell.Value)endsub单元格位移submy_offsetactivecell.offset(0,1).select当前单元格向左移动一格activecell.offset(0,-1).select当前单元格向右移动一格activecell.offset(1,0).select当前单元格向下移动一格activecell.offset(-1,0).select当前单元格向上移动一格endsub如果上述程序产生错误那是因为单元格不能移动，为了解除上述错误，我们可以往submy_offset之

17、下加一段代码onerrorresumenext注意以下代码都不再添加sub“代码名称”和endsub请自己添加!给当前单元格赋值activecell.Value=你好！给指定单元格赋值例如：单元格内容设为Range(a1).value=hello又如：你现在的工作簿在sheet1上，你要往sheet2的单元格中插入1.sheets(sheet2).selectrange(a1).value=hello或2.sheets(sheet1).Range(a1).Value=hello说明：1.sheet2被选中，然后在将“hello赋到a1单元格中。2.sheet2不必被选中，即可“hello赋到s

18、heet2的a1单元格中。隐藏工作表隐藏sheet1这张工作表sheets(sheet1).Visible=False显示sheet1这张工作表sheets(sheet1).Visible=true打印预览有时候我们想把所有的excel中的sheet都打印预览，请使用该段代码，它将在你现有的工作簿中循环，直到最后一个工作簿结束循环预览。dimmyasworksheetForeachmyinworksheetsmy.printpreviewnextmy得到当前单元格的地址msgboxactivecell.address得到当前日期及时间msgboxdate删除所有文字、批注、格式返回选定区域的行

19、数msgboxselection.Rows.count返回选定区域的列数msgboxselection.columns.count返回选定区域的地址selection.address忽略所有的错误oneRRoRResumenext遇错跳转onerrorgotoerr_handle中间的其他代码err_handle:标签跳转后的代码删除一个文件killc:1.txt定制自己的状态栏application.statusbar=现在时刻:&time恢复自己的状态栏application.statusbar=false用代码执行一个宏application.Runmacro:=text滚动窗口到a1的

20、位置activewindow.scrollRow=1activewindow.scrollcolumn=1定制系统日期dimmydate,mydaymydate=#12/12/69#myday=day(mydate)返回当天的时间dimmydate,myyearmydate=datemyyear=year(mydate)msgboxmyyearinputboxxx=inputbox(enternumberofmonthstoadd)得到一个文件名dimkkasstringkk=application.getopenFilename(excel(*.xls),*.xls,title:=提示：请打

21、开一个excel文件：)msgboxkk打开zoom对话框application.dialogs(xldialogzoom).show激活字体对话框application.dialogs(xldialogactivecellFont).show打开另存对话框dimkkasstringkk=application.getsaveasFilename(excel(*.xls),*.xls)workbooks.openkk1excle快捷键大全对工具栏进行操作的快捷键激活菜单栏，F10，alt选定前一个或下一个工具栏，ctrl+tab或ctrl+shift+tab选定工具栏中的下一个或前一个按钮或菜

22、单，tab或shift+tab(当某个工具栏被激活时)打开选定的菜单，enter运行选按钮的操作，enter在选定的文本框中输入文本，enter在对话框中使用的快捷键取消当前任务,esc单击相应命令，alt+下划线字母单击选定的按钮,enter在选项中前移，tab在选项后移,shift+tab在选项卡中前移,ctrl+tab在选项卡中后移,ctrl+shift+tab刷新“另存为”或“打开”对话框,F5插入、删除和复制选定区域的快捷键复制选定区域，ctrl+c粘贴选定区域,ctrl+V剪切选定区域,ctrl+x清除选定区域的内容，delete插入空白单元格,ctrl+shift+加号删除选定区

23、域,ctrl+-撤消最后一次操作,ctrl+z使用“office助手”需要用到的快捷键关闭“office助手”提供的消息,esc通过“office助手”获取帮助信息,F1显示下一项提示,alt+n显示前一项提示,alt+b在向导中显示或隐藏“office助手”,空格键对窗口进行操作的快捷键切换到下一个程序,alt+tab切换到前一个程序,alt+shift+tab显示windwos“开始”菜单,ctrl+esc关闭活动工作薄窗口,ctrl+w恢复活动工作薄窗口,ctrl+F5切换到下一个工作薄窗口，ctrl+F6切换到前一个工作薄窗口,ctrl+shift+F6执行“移动”命令(菜单栏中的工作薄图标菜单),ctrl+F7运行“大小”命令(菜单栏中的工作薄图标菜单),ctrl+F8将工作薄窗口最小化为图标,ctrl+F9