用excel的VBA合并汇总多个工作薄,每个工作薄的格式一样,需要合并的内容只有表头(表头5行)下面的一行(也就是只有第六行需要合并),合并后最好只显示一个表头,按表的顺序合并,求大神指点!
ãã1åå§æ°æ®æå¨å·¥ä½ç°¿å
å«å¤ä¸ªæ ¼å¼ç¸åçå·¥ä½è¡¨ï¼åªä¸è¿æ¯ä¸ªå·¥ä½è¡¨å
容ä¸åï¼æ¯å¦è¯´ä¸å人åçå·¥ä½è¡¨æ°æ®æè
ä¸åé¨é¨å¡«åçæ°æ®ã
ãã2å¨åå§æ°æ®åç®å½ä¸æ°å»ºä¸ä¸ªå·¥ä½ç°¿ï¼å»ºç«ä¸¤ä¸ªå·¥ä½è¡¨ï¼å称åå«ä¸ºâé¦é¡µâåâå并æ±æ»è¡¨âã
ãã3æAlt+F11è¿å ¥VBA代ç ç¼è¾åè°è¯çé¢ã
ãã4æ ¹æ®æ示ï¼æå ¥ä¸ä¸ªæ¨¡åã
ãã5å°ä¸è¿°ä»£ç ç²è´´å°æ¨¡å空ç½å¤ï¼
ããSub CombineSheetsCells()
ããDim wsNewWorksheet As Worksheet
ããDim cel As Range
ããDim DataSource, RowTitle, ColumnTitle, SourceDataRows, SourceDataColumns As Variant
ããDim TitleRow, TitleColumn As Range
ããDim Num As Integer
ããDim DataRows As Long
ããDataRows = 1
ããDim TitleArr()
ããDim Choice
ããDim MyName$, MyFileName$, ActiveSheetName$, AddressAll$, AddressRow$, AddressColumn$, FileDir$, DataSheet$, myDelimiter$
ããDim n, i
ããn = 1
ããi = 1
ããApplication.DisplayAlerts = False
ããWorksheets("å并æ±æ»è¡¨").Delete
ããSet wsNewWorksheet = Worksheets.Add(, after:=Worksheets(Worksheets.Count))
ããwsNewWorksheet.Name = "å并æ±æ»è¡¨"
ããMyFileName = Application.GetOpenFilename("Excelå·¥ä½è (*.xls*),*.xls*")
ããIf MyFileName = "False" Then
ããMsgBox "没æéæ©æ件ï¼è¯·éæ°éæ©ä¸ä¸ªè¢«å并æ件ï¼", vbInformation, "åæ¶"
ããElse
ããWorkbooks.Open Filename:=MyFileName
ããNum = ActiveWorkbook.Sheets.Count
ããMyName = ActiveWorkbook.Name
ããSet DataSource = Application.InputBox(prompt:="请éæ©è¦å并çæ°æ®åºåï¼", Type:=8)
ããAddressAll = DataSource.Address
ããActiveWorkbook.ActiveSheet.Range(AddressAll).Select
ããSourceDataRows = Selection.Rows.Count
ããSourceDataColumns = Selection.Columns.Count
ããApplication.ScreenUpdating = False
ããApplication.EnableEvents = False
ããFor i = 1 To Num
ããActiveWorkbook.Sheets(i).Activate
ããActiveWorkbook.Sheets(i).Range(AddressAll).Select
ããSelection.Copy
ããActiveSheetName = ActiveWorkbook.ActiveSheet.Name
ããWorkbooks(ThisWorkbook.Name).Activate
ããActiveWorkbook.Sheets("å并æ±æ»è¡¨").Select
ããActiveWorkbook.Sheets("å并æ±æ»è¡¨").Range("A" & DataRows).Value = ActiveSheetName
ããActiveWorkbook.Sheets("å并æ±æ»è¡¨").Range(Cells(DataRows, 2), Cells(DataRows, 2)).Select
ããSelection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
ããSkipBlanks:=False, Transpose:=False
ããSelection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
ããFalse, Transpose:=False
ããSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
ãã:=False, Transpose:=False
ããDataRows = DataRows + SourceDataRows
ããWorkbooks(MyName).Activate
ããNext i
ããApplication.ScreenUpdating = True
ããApplication.EnableEvents = True
ããEnd If
ããWorkbooks(MyName).Close
ããEnd Sub
ãã6å¨âé¦é¡µâå·¥ä½è¡¨ä¸æä¸å¾ç¤ºèæå ¥ä¸ä¸ªçªä½æ§ä»¶å¹¶æå®å®ä¸ºæå ¥ç代ç å称ã
ãã7ç¹å»âé¦é¡µâå·¥ä½è¡¨ä¸æå ¥çæé®ï¼æ ¹æ®æ示ï¼æµè§å°åå§æ°æ®å·¥ä½ç°¿ã
ãã8ä¸ä¸æ¥ï¼ç¨é¼ æ éæ©è¦å并çæ°æ®èå´ã
ãã注æï¼æ¯ä¸ªå·¥ä½è¡¨æ°æ®å¯è½ä¸ä¸æ ·ï¼æ¯å¦è¯´æçæ¯10è¡æ°æ®ï¼æçæ¯30è¡æ°æ®ãå¨è¿éæ们å¯ä»¥ç¨é¼ æ éæ©ä»»æå·¥ä½è¡¨çä¸ä¸ªè¾å¤§èå´ï¼æ¯å¦è¯´A1:D100ï¼ä¿è¯æ¯æå¤è¡æ°çå·¥ä½è¡¨æ°æ®è¿å¤å°±å¯ä»¥ï¼ä¸ä¼åå é¤ç©ºè¡ã
ãã9ç¹å»ç¡®å®æé®ï¼å¾ 代ç è¿è¡å®æ¯åï¼ææçæ°æ®å°±é½å并å°äºâå并æ±æ»è¡¨âä¸ã
ãã注æï¼
ãã1ï¼Aåçææ¬è¯´æå³ä¾§çæ°æ®æ¥èªäºåå§æ°æ®è¡¨çåªä¸ªå·¥ä½è¡¨ï¼
ãã2ï¼æ°æ®ä¹é´ä¼æä¸äºç©ºè¡ï¼ä¸é¢éè¿çéå é¤ã
ãã10éä¸å ¨é¨æ°æ®åºåï¼æ§è¡èªå¨çéãç¶åéæ©å ¶ä¸ä¸ä¸ªå段ï¼éæ©â空ç½âåæ é¢å 容ã
ãã11ç¶åå°çéåºæ¥çæ ç¨è¡é¼ æ å³é®å é¤ï¼åå é¤Aåæ´åå³å¯ï¼å®æææ追é®
ãã2å¨åå§æ°æ®åç®å½ä¸æ°å»ºä¸ä¸ªå·¥ä½ç°¿ï¼å»ºç«ä¸¤ä¸ªå·¥ä½è¡¨ï¼å称åå«ä¸ºâé¦é¡µâåâå并æ±æ»è¡¨âã
ãã3æAlt+F11è¿å ¥VBA代ç ç¼è¾åè°è¯çé¢ã
ãã4æ ¹æ®æ示ï¼æå ¥ä¸ä¸ªæ¨¡åã
ãã5å°ä¸è¿°ä»£ç ç²è´´å°æ¨¡å空ç½å¤ï¼
ããSub CombineSheetsCells()
ããDim wsNewWorksheet As Worksheet
ããDim cel As Range
ããDim DataSource, RowTitle, ColumnTitle, SourceDataRows, SourceDataColumns As Variant
ããDim TitleRow, TitleColumn As Range
ããDim Num As Integer
ããDim DataRows As Long
ããDataRows = 1
ããDim TitleArr()
ããDim Choice
ããDim MyName$, MyFileName$, ActiveSheetName$, AddressAll$, AddressRow$, AddressColumn$, FileDir$, DataSheet$, myDelimiter$
ããDim n, i
ããn = 1
ããi = 1
ããApplication.DisplayAlerts = False
ããWorksheets("å并æ±æ»è¡¨").Delete
ããSet wsNewWorksheet = Worksheets.Add(, after:=Worksheets(Worksheets.Count))
ããwsNewWorksheet.Name = "å并æ±æ»è¡¨"
ããMyFileName = Application.GetOpenFilename("Excelå·¥ä½è (*.xls*),*.xls*")
ããIf MyFileName = "False" Then
ããMsgBox "没æéæ©æ件ï¼è¯·éæ°éæ©ä¸ä¸ªè¢«å并æ件ï¼", vbInformation, "åæ¶"
ããElse
ããWorkbooks.Open Filename:=MyFileName
ããNum = ActiveWorkbook.Sheets.Count
ããMyName = ActiveWorkbook.Name
ããSet DataSource = Application.InputBox(prompt:="请éæ©è¦å并çæ°æ®åºåï¼", Type:=8)
ããAddressAll = DataSource.Address
ããActiveWorkbook.ActiveSheet.Range(AddressAll).Select
ããSourceDataRows = Selection.Rows.Count
ããSourceDataColumns = Selection.Columns.Count
ããApplication.ScreenUpdating = False
ããApplication.EnableEvents = False
ããFor i = 1 To Num
ããActiveWorkbook.Sheets(i).Activate
ããActiveWorkbook.Sheets(i).Range(AddressAll).Select
ããSelection.Copy
ããActiveSheetName = ActiveWorkbook.ActiveSheet.Name
ããWorkbooks(ThisWorkbook.Name).Activate
ããActiveWorkbook.Sheets("å并æ±æ»è¡¨").Select
ããActiveWorkbook.Sheets("å并æ±æ»è¡¨").Range("A" & DataRows).Value = ActiveSheetName
ããActiveWorkbook.Sheets("å并æ±æ»è¡¨").Range(Cells(DataRows, 2), Cells(DataRows, 2)).Select
ããSelection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
ããSkipBlanks:=False, Transpose:=False
ããSelection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
ããFalse, Transpose:=False
ããSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
ãã:=False, Transpose:=False
ããDataRows = DataRows + SourceDataRows
ããWorkbooks(MyName).Activate
ããNext i
ããApplication.ScreenUpdating = True
ããApplication.EnableEvents = True
ããEnd If
ããWorkbooks(MyName).Close
ããEnd Sub
ãã6å¨âé¦é¡µâå·¥ä½è¡¨ä¸æä¸å¾ç¤ºèæå ¥ä¸ä¸ªçªä½æ§ä»¶å¹¶æå®å®ä¸ºæå ¥ç代ç å称ã
ãã7ç¹å»âé¦é¡µâå·¥ä½è¡¨ä¸æå ¥çæé®ï¼æ ¹æ®æ示ï¼æµè§å°åå§æ°æ®å·¥ä½ç°¿ã
ãã8ä¸ä¸æ¥ï¼ç¨é¼ æ éæ©è¦å并çæ°æ®èå´ã
ãã注æï¼æ¯ä¸ªå·¥ä½è¡¨æ°æ®å¯è½ä¸ä¸æ ·ï¼æ¯å¦è¯´æçæ¯10è¡æ°æ®ï¼æçæ¯30è¡æ°æ®ãå¨è¿éæ们å¯ä»¥ç¨é¼ æ éæ©ä»»æå·¥ä½è¡¨çä¸ä¸ªè¾å¤§èå´ï¼æ¯å¦è¯´A1:D100ï¼ä¿è¯æ¯æå¤è¡æ°çå·¥ä½è¡¨æ°æ®è¿å¤å°±å¯ä»¥ï¼ä¸ä¼åå é¤ç©ºè¡ã
ãã9ç¹å»ç¡®å®æé®ï¼å¾ 代ç è¿è¡å®æ¯åï¼ææçæ°æ®å°±é½å并å°äºâå并æ±æ»è¡¨âä¸ã
ãã注æï¼
ãã1ï¼Aåçææ¬è¯´æå³ä¾§çæ°æ®æ¥èªäºåå§æ°æ®è¡¨çåªä¸ªå·¥ä½è¡¨ï¼
ãã2ï¼æ°æ®ä¹é´ä¼æä¸äºç©ºè¡ï¼ä¸é¢éè¿çéå é¤ã
ãã10éä¸å ¨é¨æ°æ®åºåï¼æ§è¡èªå¨çéãç¶åéæ©å ¶ä¸ä¸ä¸ªå段ï¼éæ©â空ç½âåæ é¢å 容ã
ãã11ç¶åå°çéåºæ¥çæ ç¨è¡é¼ æ å³é®å é¤ï¼åå é¤Aåæ´åå³å¯ï¼å®æææ追é®
æå°±æ³é®ä¸ï¼æææ表åªå¤å¶æ é¢ä¸é¢çä¸è¡ï¼è¯·é®è¿ä¸ªä»£ç æä¹åï¼é常æè°¢ï¼
温馨提示:答案为网友推荐,仅供参考