excel常用宏.docx
《excel常用宏.docx》由会员分享，可在线阅读，更多相关《excel常用宏.docx（15页珍藏版）》请在冰豆网上搜索。
excel常用宏
1.拆分单元格赋值
Sub拆分填充（）
DimxAsRange
ForEachxInActiveSheet.UsedRange.Cells
Ifx.MergeCellsThen
x.Select
x.UnMerge
Selection.Value=x.Value
EndIf
Nextx
EndSub
2.Excel 宏 按列拆分多个excel
SubMacro1（）
DimwbAsWorkbook,arr,rngAsRange,dAsObject,k,t,shAsWorksheet,i&
Setrng=Range（"A1:
f1"）
Application.ScreenUpdating=False
Application.DisplayAlerts=False
arr=Range（"a1:
a"&Range（"b"&Cells.Rows.Count）.End（xlUp）.Row）
Setd=CreateObject（"scripting.dictionary"）
Fori=2ToUBound（arr）
IfNotd.Exists（arr（i,1））Then
Setd（arr（i,1））=Cells（i,1）.Resize（1,13）
Else
Setd（arr（i,1））=Union（d（arr（i,1））,Cells（i,1）.Resize（1,13））
EndIf
Next
k=d.Keys
t=d.Items
Fori=0Tod.Count-1
Setwb=Workbooks.Add（xlWBATWorksheet）
Withwb.Sheets
（1）
rng.Copy.[A1]
t（i）.Copy.[A2]
EndWith
wb.SaveAsFilename:
=ThisWorkbook.Path&"\"&k（i）&".xlsx"
wb.Close
Next
Application.DisplayAlerts=True
Application.ScreenUpdating=True
MsgBox"完毕"
EndSub
3.Excel 宏 按列拆分多个sheet
在一个工作表中是许多的公司订单记录，如何将它按公司名分拆成一个个工作表，用VBA实现相当便捷。
以下是演试：
原始工作簿：
运行VBA代码后的工作簿：
代码如下：
1.需要先把数据按照分拆的那一列字段排序
2.如果你想应用在你的表格中，只需将所有resize（1,3）中的3修改，改成你的表格的列数。
如果你总表有8列就改成resize（1,8）即可
3.如果你想根据表格的第一列拆分，需要把Sheet1.Cells（i,2）<>Sheet1.Cells（i-1,2）和sh.Name=Sheet1.Cells（i,2）的2换成1
Subs（）
Application.ScreenUpdating=False
DimshAsWorksheet,iAsInteger
Fori=2ToSheet1.[a65536].End（3）.Row
If Sheet1.Cells（i,2）<>Sheet1.Cells（i-1,2） Then
Worksheets.Addafter:
=Worksheets（Sheets.Count）
Setsh=ActiveSheet
sh.Name=Sheet1.Cells（i,2）
sh.Range（"a1"）.Resize（1,3）.Value=Sheet1.Range（"a1"）.Resize（1,3）.Value
sh.Range（"a65536"）.End（3）.Offset（1,0）.Resize（1,3）.Value=Sheet1.Cells（i,1）.Resize（1,3）.Value
Else
sh.Range（"a65536"）.End（3）.Offset（1,0）.Resize（1,3）.Value=Sheet1.Cells（i,1）.Resize（1,3）.Value
EndIf
Nexti
Application.ScreenUpdating=True
EndSub
4.Excel 宏 多工作表合并
FunctionLastRow（shAsWorksheet）
OnErrorResumeNext
LastRow=sh.Cells.Find（what:
="*",_
After:
=sh.Range（"A1"）,_
Lookat:
=xlPart,_
LookIn:
=xlFormulas,_
SearchOrder:
=xlByRows,_
SearchDirection:
=xlPrevious,_
MatchCase:
=False）.Row
OnErrorGoTo0
EndFunction
Subs（）
DimshAsWorksheet
DimDestShAsWorksheet
DimLastAsLong
DimshLastAsLong
DimCopyRngAsRange
DimStartRowAsLong
Application.ScreenUpdating=False
Application.EnableEvents=False
'新建一个“汇总〞工作表
Application.DisplayAlerts=False
OnErrorResumeNext
ActiveWorkbook.Worksheets（"汇总"）.Delete
OnErrorGoTo0
Application.DisplayAlerts=True
SetDestSh=ActiveWorkbook.Worksheets.Add
DestSh.Name="汇总"
'开场复制的行号，忽略表头，无表头请设置成1
StartRow=2
ForEachshInActiveWorkbook.Worksheets
Ifsh.Name<>DestSh.NameThen
Last=LastRow（DestSh）
shLast=LastRow（sh）
IfshLast>0AndshLast>=StartRowThen
SetCopyRng=sh.Range（sh.Rows（StartRow）,sh.Rows（shLast））
IfLast+CopyRng.Rows.Count>DestSh.Rows.CountThen
MsgBox"容太多放不下啦！
"
GoToExitSub
EndIf
CopyRng.Copy
WithDestSh.Cells（Last+1,"A"）
.PasteSpecialxlPasteValues
.PasteSpecialxlPasteFormats
Application.CutCopyMode=False
EndWith
EndIf
EndIf
Next
ExitSub:
Application.GotoDestSh.Cells
（1）
DestSh.Columns.AutoFit
Application.ScreenUpdating=True
Application.EnableEvents=True
EndSub
5.多个sheet拆成多个excel
Sub Macro1（） Dim sht As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each sht In Sheets sht.Copy ActiveWorkbook.SaveAs Filename:
=ThisWorkbook.Path & "\" & sht.Name & ".xlsx" ActiveWorkbook.Close Next Application.DisplayAlerts = True Application.ScreenUpdating = TrueEnd Sub
或者
PrivateSub分拆工作表（） DimshtAsWorksheet DimMyBookAsWorkbook SetMyBook=ActiveWorkbook ForEachshtInMyBook.Sheets sht.Copy ActiveWorkbook.SaveAsFilename:
=MyBook.Path&"\"&sht.Name,FileFormat:
=xlNormal '将工作簿另存为EXCEL默认格式 ActiveWorkbook.Close Next MsgBox"文件已经被分拆完毕!
" EndSub
6.利用txt提取文件夹中的所有文件名称
1、在那个文件夹新建一个.TXT文件〔如wenjian.txt〕，用记事本单开输入dir>1.txt保存退出将刚刚的.TXT〔wenjian.txt〕更名为.bat文件〔wenjian.bat〕双击wenjian.bat文件运行一次，在文件夹多出一个1.txt文件翻开1.txt文件，将其中的容粘贴到Excel中，数据——分列处理就可以得到你要的文件名列表了！
7.一列拆成两列
Excel电子表格的功能非常强大，无论是拆分还是合并单元格都可以轻松完成。
有时候我们编辑数据的时候将“名称〞和“价格〞全部放到了一个单元格中了，有什么方法可以快速将这些数据拆分开呢？
下面Word联盟以具体实例来为大家详细介绍操作方法。
Excel表格中的数据拆分
案例说明：
水果名称与水果价格全部在一个单元格中，只是用“空格〞分隔开。
我们将这些以空格分隔开的数据分别拆分到两个单元格中。
①首先，我们在Excel表格中选中需要拆分的列；
②然后，单击菜单栏的“数据〞，在下拉列表中选择“分列〞命令；
③此时，需要3个步骤来完成数据在表格中的拆分，“文本分列向导-3步骤之1〞，我们只需选择默认的“分割符号〞再单击下面的“下一步〞按钮；
④然后，继续在“文本分列向导-3步骤之2〞下面的“分隔符号〞中勾选“Tab键〞、“空格〞和“连续分隔符号视为单个处理〞。
〔现在我们可以在“数据预览〞
升级会员 