自動按列分組拆分excel工作表

僅供個人參考分excel工excel以達(dá)或者vbs的excel因為大量文檔ALT+F11進(jìn)---【模塊】F5，不得用于商業(yè)用途

僅供個人參考建碼開始SubCFGZB()DimmyRangeAsVariantDimDimtitleRangeAsRangeDimtitleAsStringDimShNameStringDimcolumnNumIntegermyRange=請擇標(biāo)題行",Type:=8)myArray=WorksheetFunction.Transpose(myRange)SettitleRange=Application.InputBox(prompt:="請擇拆分的表頭，必須是第一行，且為一個單元格，如：姓",Type:=8)titletitleRange.Value=titleRange.ColumnApplication.Volatile=Application.ScreenUpdating=FalseApplication.DisplayAlerts=FalseDimi&,Myr&,Arr,num&Dimd,kFor=Sheets.Count1Step-1Sheets(i).Name<>Sheets(i).DeleteEndIfNextiSet=CreateObject("Scripting.Dictionary")Worksheets(ShName).Arr=Worksheets(ShName).Range(Cells(2,columnNum),Cells(Myr,columnNum))For=Tod(Arr(i,1))=Nextkd.keysFor=Tok(i)<>Set=CreateObject("adodb.connection")conn.Open"provider=;extendedproperties=excel8.0;datasource="&ThisWorkbook.FullNameSql"select*["&"&title&"='"&k(i)"'"Worksheets.Addafter:=Sheets(Sheets.Count)WithActiveSheet.Namek(i)For=1ToUBound(myArray)不得用于商業(yè)用途

僅供個人參考.Cells(1,num)=myArray(num,1)Next.Range("A2").CopyFromRecordsetconn.Execute(Sql)EndSheets(1).SelectSheets(1).Cells.SelectSelection.CopyWorksheets(Sheets.Count).ActivateSelection.PasteSpecialPaste:=xlPasteFormats,Operation:=xlNone,_SkipBlanks:=False,Transpose:=FalseApplication.CutCopyMode=FalseEndIfNexticonn.CloseSet=NothingApplication.DisplayAlerts=TrueApplication.ScreenUpdating=True‘分至工作表完畢，開始拆分至單獨(dú)件，如無需拆分至文件，請將以下代碼刪除，保留最后一行EndSub結(jié)語DimshtAsWorksheetDimMyBookAsWorkbookSetMyBook=Setfso=CreateObject("scripting.filesystemobject")fso.createfolder(MyBook.Path"\"&ForshtMyBook.Sheetssht.Name<>Thensht.CopyActiveWorkbook.SaveAsFilename:=MyBook.Path&"\"&ShName"\"&sht.Name,FileFormat:=xlNormalActiveWorkbook.CloseEndIfNextMsgBox文已經(jīng)被拆完!EndSub‘vbs代結(jié)束不得用于商業(yè)用途

僅供個人參考僅供個用學(xué)習(xí)、究不得用商業(yè)用。Forpersonaluseonlyinstudyandresearch;notforcommercialuse.Nurfürdenpers?nlichenfürStudien,Forschung,zukommerziellenZweckenverwendetwerden.Pourl'étudeetrechercheuniquementàdesfinspersonnelles;pasàdesfinscommerciales.тольк