excel技巧.doc

打开excel，按alt+f11，在sheet1（sheet）右边的框框里面copy下面这些代码，再点击开始运行，就是类似的这个标志，就可以了，后面你照着弹出来框框操作就行了。宏：多个文件表合到一个文件表的多个SHEET中Sub CombineWorkbooks()Dim FilesToOpen, ftDim x As IntegerApplication.ScreenUpdating = FalseOn Error GoTo errhandlerFilesToOpen = Application.GetOpenFilename _(FileFilter:=Micrsofe Excel文件(*.xls), *.xls, _MultiSelect:=True, Title:=要合并的文件)If TypeName(FilesToOpen) = boolean ThenMsgBox 没有选定文件GoTo errhandlerEnd Ifx = 1While x = UBound(FilesToOpen)Set wk = Workbooks.Open(Filename:=FilesToOpen(x)wk.Sheets().Move after:=ThisWorkbook.Sheets _(ThisWorkbook.Sheets.Count)x = x + 1WendMsgBox 合并成功完成！errhandler:MsgBox Err.DescriptionResume errhandlerEnd Sub这个同我猜的就一样，需要VB来写了，抄袭下了 .复制以下代码到宏，然后在要汇总的表上创建一个对象（文本框或者图片），然后在其身上点右键，指定宏，选择我们保存的宏，汇总时，点对象即可执行汇总操作。以下就是宏内容：Sub 将同路径下的多张工作薄中的工作表合并到当前活动的工作表() Application.ScreenUpdating = False Dim lj, dirname, nm Dim a As Long Dim i As Long lj = ActiveWorkbook.Path nm = ActiveWorkbook.Name dirname = Dir(lj & *.xls) Do While dirname If dirname nm Then Workbooks.Open Filename:=lj & & dirname a = Sheets.Count 读当前工作薄中的所有的工作表 Workbooks(nm).Activate For i = 1 To a Workbooks(dirname).Sheets(i).UsedRange.Copy Range(a65536).End(xlUp).Offset(1, 0) 复制新打开的工作簿的第一个工作表的已用区域到rng Next i Workbooks(dirname).Close False End If dirname = Dir Loop End Sub好用：将这些文件copy到一个文件夹（只有这些Excel，且若打开某一文件，数据就能看见即不用点其他sheet），建一新Excel，也存到该文件夹。仅打开该新Excel，按Alt+F11，左边窗口右键点，插入模块，在右边窗口粘贴如下代码：Sub Find()Application.ScreenUpdating = FalseDim MyDir As StringMyDir = ThisWorkbook.Path & ChDrive Left(MyDir, 1) find all the excel filesChDir MyDirMatch = Dir$()DoIf Not LCase(Match) = LCase(ThisWorkbook.Name) ThenWorkbooks.Open Match, 0 openActiveSheet.Copy Before:=ThisWorkbook.Sheets(1) copy sheetWindows(Match).ActivateActiveWindow.CloseMatch = Dir$End IfLoop Until Len(Match) = 0Application.ScreenUpdating = TrueEnd Sub直接按F5运行该宏，这些文件copy到了一起。然后你可以用诸如 =sum(sheet2:sheet13!B2) 把所有sheet的B2的和汇总。按下ALT+F11打开VBA编辑器，插入一个模块，将下列代码粘贴进去使用即可。以下为需要复制的代码：Sub SheetSort()工作表按名称排序Dim i As LongDim Sh As Worksheet关闭屏幕刷新以提高速度Application.ScreenUpdating = False添加一张临时表提取所有表名并排序ThisWorkbook.Worksheets.Add after:=Worksheets(Worksheets.Count)Set Sh = ActiveSheetFor i = 1 To Worksheets.Count - 1 Sh.Cells(i, 1) = Worksheets(i).NameNextSh.Columns(1).Sort Key1:=Range(A1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:= _ xlPinYin, DataOption1:=xlSortNormal 按名称排序后移动表For i = 1 To Worksheets.Count - 1 Worksheets(Sh.Cells(i, 1).Text).Move after:=Worksheets(Worksheets.Count)Next禁止提示删除临时表再恢复提示Application.DisplayAlerts = FalseSh.DeleteApplication.DisplayAlerts = True恢复屏幕刷新Application.ScreenUpdating = TrueEnd Sub工作表排序SubSort_Sheets() DimsCountAsInteger,IAsInteger,RAsInteger ReDimNa(0)AsString sCount=Sheets.Count ForI=1TosCount ReDimPreserveNa(I)AsString Na(I)=Sheets(I).Name Next ForI=1To