(完整)excel常用宏

1.拆分单元格赋值Sub 拆分填充()DimxAsRangeForEachxInActiveSheet。UsedRange.CellsIfx。MergeCellsThenx.Selectx.UnMergeSelection.Value=x。ValueEndIfNextxEndSub2. Excel 宏按列拆分多个 excelSubMacro1()DimwbAsWorkbook，arr，rngAsRange,dAsObject,k，t,shAsWorksheet,i&Setrng=Range("A1：f1")Application。ScreenUpdating=FalseApplication。DisplayAlerts=Falsearr=Range("a1:a”&Range("b”&Cells.Rows。Count).End(xlUp)。Row)Setd=CreateObject("scripting。dictionary")Fori=2ToUBound(arr)IfNotd.Exists(arr(i,1))ThenSetd(arr(i,1))=Cells(i,1)。Resize(1,13)ElseSetd(arr(i,1))=Union(d(arr(i,1)),Cells(i,1).Resize(1,EndIfNextk=d.Keyst=d.ItemsFori=0TodoCount-1Setwb=Workbooks。Add(xlWBATWorksheet)WithwboSheets(1)rngoCopy。[A1]t(i)。Copy。[A2]EndWithwb.SaveAsFilename:=ThisWorkbook.Path&”\”&k(i)＆".xlsx"wboCloseNextApplication。DisplayAlerts=TrueApplication.ScreenUpdating=TrueMsgBox"完毕"EndSub13))3. Excel 宏按列拆分多个 sheet在一个工作表中是许多的公司订单记录，如何将它按公司名分拆成一个个工作表，用 VBA 实现相当便捷。以下是演试：原始工作簿：sh.Range(”a1").Resize(1,3)。Value=Sheet1。Range("a1")。Resize(1,3).Valuesh.Range(”a65536”).End(3).Offset(1,0).Resize(1,3)oValue=Sheet1。Cells(i,1).Resize(1,3)。ValueElseshoRange(”a65536”)。End(3).Offset(1,0)。Resize(1,3).Value=Sheetl.Cells(i,1)。Resize(1,3)。ValueEndIfNextiApplication.ScreenUpdating=TrueEndSubABCD1公司编号 公司名订单号2El21XX 百货65453El21XX 百货.35'24El21XX 白货23455&运行 VBA 代码后的工作簿:4. Excel 宏多工作表合并FunctionLastRow(shAsWorksheet)OnErrorResumeNextLastRow=sh。Cells.Find(what:二”*",_After：=sh.Range("A1")，_Lookat:=xlPart，_LookIn:=xlFormulas,_SearchOrder:=xlByRows，_SearchDirection:=xlPrevious，_MatchCase:二False)。RowOnErrorGoTo0EndFunctionSubs()DimshAsWorksheetDimDestShAsWorksheetDimLastAsLongDimshLastAsLongDimCopyRngAsRangeDimStartRowAsLongApplication.ScreenUpdating=FalseApplication。EnableEvents=False'新建一个“汇总”工作表Application。DisplayAlerts=FalseOnErrorResumeNextActiveWorkbook.Worksheets("汇总"...