將 Excel 活頁簿中的多個 工作表合併 為一個

62

最近常常在進行手動的 EXCEL 工作表合併 工作

就是常常會需要將多個工作表(Worksheets)裡的資料合併在一起,如下圖所示

但如果用人工來處理,就需要一直重覆著 Crtl + A(全選)、Crtl + C(複製)、Crtl + V(貼上) 的標準動作 ( ´•̥̥̥ω•̥̥̥` )

工作表合併

為解決上述的困擾,所以就弄了一段 VBA 的程式碼,來處理這重覆的人工處理作業過程

跟蘋果米有著一樣困擾的冰友們,可以跟著蘋果米列在下面的步驟一起來操作喔 ლ(^o^ლ)

利用 VBA 進行 EXCEL 工作表合併

操作步驟

  1. 首先按下快鍵盤快速鍵 ALT + F11,接著會開啟 Microsoft Visual Basic for Applications 視窗
  2. 接著點擊上方選單的 插入(I) > 模組(M),接著把下方的程式碼貼入程式碼的視窗中
  3. 再來按下鍵盤上的 F5 按鍵,會跳出一個巨集儲存的視窗,最後按下 ⌈執行⌋ 按鍵,就會將所有的資料表(Worksheets) 都合併到一個名稱叫做 ⌈Combined⌋ 的新工作表了喔
Sub Combine()
    Dim J As Integer
    
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    
    For J = 2 To Sheets.Count
        Sheets(J).Activate
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
        //只需要複製欄位值,請註解掉上一行程式碼,並將下列二行的註解拿掉
        //Selection.Copy
        //Sheets(1).Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
    Next
End Sub

注意事項

  1. 所有的工作表(Worksheets) 的資料都必需由欄 A1 開始
  2. 所有的工作表(Worksheets) 的資料都必需是同樣的結構
  3. 該段 VB 的巨集程式碼,只適用單一個活頁簿(WorkBook)利
Share.

62 篇迴響

  1. 我先用了大大的方法(將多個 Excel 活頁簿 (Workbook) 合併 為一個)的VBA再用合併,但是沒辦法成功
    可以提供一個可行的VBA嗎?感激不盡

    • Hi hEI,
      沒辦法成功,是出了什麼錯誤訊息呢?
      這樣的問法,是無法幫到你的喔

  2. 蜂蜜 Windows other version Google Chrome 56.0.2924.87 on

    請問大大

    合併完後,別的表格插入內容 合併完的不會更著動 要如何改 ?

    如何讓其他表格同步?

  3. memo Windows other version Google Chrome 56.0.2924.87 on

    您好,
    因為對vba的知識尚淺,就一行一行慢慢去理解..
    覺得會程式的人 真不簡單

    目前有些困惑的地方是上列程式碼中的
    第8行為何是.activate而不是.select呢?
    第17行,為何不是使用End(xldown)而是xlup呢? Een(xlUp)後的(2)又是什麼意思呢?

    能解答問題的話 我會很感激的QwQ/

    • 1. 第8行為何是.activate而不是.select呢?
      ANS : 因為要將第二個worksheet設為作用中

      2. 第17行,為何不是使用End(xldown)而是xlup呢?
      ANS : 因為我是從第65536行往上找

      3. Endn(xlUp)後的(2)又是什麼意思呢?
      ANS : 因為不複製首行(所以做了位移)

  4. NANA Windows other version Google Chrome 56.0.2924.87 on

    大大,還想再請教您一個問題,如果不要每次執行都是新增合併的工作表而是更新,要如何修改呢

  5. NANA Windows other version Google Chrome 56.0.2924.87 on

    您好,如果我只想要合併值,不要帶公式,請問要如何修改呢~~謝謝

    • Hi NANA,
      只要複製欄位的值而不要公式的作法
      已經更新在文章的程式碼中了
      請自行取用喔

  6. Daniel Mac OS X Safari 10.0 on

    請問我只有要合併前6個工作表,要修改哪一部份的程式呢?謝謝

  7. Hi Jason 翰 ,

    先謝謝你這個程式 , 想請問一下 , 如果想連A1欄一起覆製 , 程式是否哪邊修改一下就可以了呢 ?

    • Hi Amy,
      將下列這句語法標註掉或刪掉即可
      Selection.Offset(1, 0).Resize(Selection.Rows.Count – 1).Select

  8. Hi Jason 翰 ,

    先謝謝你這個程式 , 想請問一下 , 如果想連A1欄一起覆製 , 程式是否哪邊修改一下就可以了呢 ?

    • Hi 苡佑,
      將下列這句語法標註掉或刪掉即可
      Selection.Offset(1, 0).Resize(Selection.Rows.Count – 1).Select

  9. 成功了,但是我還需要當其中的一個sheet有更新時合併的檔案也跟著更新~
    請問大大有辦法嗎?

  10. Penny Windows other version Google Chrome 52.0.2743.116 on

    您好, 想請問合併後combined工作表裡只顯示最後一個工作表的內容, 其他都沒跑出來, 是為什麼呢? 先謝謝解答了.

    • Hi Penny,
      可以請您把工作表寄給蘋果米瞧瞧嗎?
      聯絡信箱: service [at]appleme.club (請將 [at]替換成 @)

  11. 您好! 謝謝您的貢獻與分享,但我輸入後,Combined工作表只出現每個工作表的標題,無內容,請問該如何處理? 謝謝!

    • 米雪,
      您提供的資訊有點少, 蘋果米無法判斷耶
      可否提供您說的工作表以助蘋果米了解您的問題

    • Hi, 你好~
      我本身有10個工作表,希望集合在同一工作表後,進行樞紐分析, 我照著文中提到的方式進行,最後得到的解果是在COMBINED的工作表內,只有出現10個工作表的表頭, A,B,C,D,E2後的資料(內容)都沒有出現, 例: 在 A1(名稱), B1(敘述), C1(日期), D1(數量), E1(價格), F1(狀態)
      謝謝!

    • Hi 米雪,
      試著把你的檔案找個雲端空間儲存
      然後蘋果米就能去下回來看看
      這樣才能協助解決你的問題

    • 您好,在https://appleme.club/about/advertise/網址輸入後,出現以下資訊Failed to send your message. Please try later or contact the administrator by another method.

    • Hi 米雪,
      最近蘋果米的系統有點問題,您可以先用蘋果米的FB私訊功能提供檔案喔

  12. Andrew Windows other version Unknow on

    你好, 這真的蠻實用的, 我有幾個問題想要請教一下
    1. 如果我的 sheets 不是全都連在一起的,中間可能有多幾個不等的 sheets 話我解決這個問題呢?
    2. 如果我想保留最上層的名稱欄位,再全部合併在一起, 這樣有辦法嗎?
    3. 合併的數值中有公式, 可以貼成文字嗎? 因為試過了公式的執會跑掉
    感謝你

    • Hi Andrew,
      您的問題蘋果米到現在才看到
      蘋果米會試著了解你的問題
      但可否提供範例檔給蘋果米呢?

  13. 若所有的工作表(Worksheets) 的資料都由欄 A4 開始~那該如何修改?

  14. Sibyl Mac OS X Safari 9.0 on

    Hi Jason翰,
    不好意思!在請問一下,如果我excel裡面有一個sheet(upload file)不要被合併,其他的都要合併,並且指定合併到sheet1,應該要怎麼改?
    謝謝

  15. Sibyl Mac OS X Safari 9.0 on

    Hi Jason翰,不知道你有沒有看到我的提問!希望你可以幫幫忙~~

  16. Sibyl Mac OS X Safari 9.0 on

    如果想在combine的sheet裡多加一列 來源的sheet 名稱,讓每一筆資料後面都有 原來sheet的名稱, 應該怎麼做?

    • Hi Sibyl,
      目前這個架構的程式碼,暫無支援您的需求
      如果要做到該功能,可能要再寫另一段程式碼才會合用

  17. Sibyl Mac OS X Safari 9.0 on

    我想請問:如果我要合併幾個不同的檔案到一個新的檔案!要怎麼寫?

  18. 你好 我嘗試您的CODE發現,SHEET.ADD 之後,SHEET2的第一筆資料不會結合到COMBINED裡

  19. 正好需要用到,非常有幫助!另想請問有沒有可能在原本的工作頁新增異動時,⌈Combined⌋ 的新工作表也會跟著更新?

  20. DEPON Windows other version Firefox 43.0 on

    您好,請問我分頁行中有空格把檔案分段,好像就沒把用此方法將全部資料合併,空格以下的內容就無法合併到新分頁,請問有辦法連空格底下的檔案一起合併嗎??謝謝!!

    • Hi Depon,

      試試將這段語法

      Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
      

      換成下面這段

      Selection.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Select
      
  21. 不好意思,小弟是個新手,正在網上爬文學習

    而最近有個需求剛好跟這段VBA很相近,

    但還想要加一些東西,無奈有點卡關…

    我這邊的理想上 是做一個 包含

    1. 固定開某個.xls 的 file
    2. 其中有6個 sheet, 但我只想要合併其中兩個sheet (如此篇文章, 這兩個sheet 格式都一樣)
    3. 最後將合併後的 sheet (如此篇的”combined”) 在同資料夾,另存成獨立的CSV檔 (combined.csv)

    不知J大是否能幫個忙 做個範例給我reference一下..

    感謝!

    • Hi CH,
      把 for 迴圈的語法拿掉, 原本包在裏面的內容寫死, 即能達到你的需求

      Sheets(2).Activate
      Range("A1").Select
      Selection.CurrentRegion.Select
      Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
      Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
      
      Sheets(3).Activate
      Range("A1").Select
      Selection.CurrentRegion.Select
      Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
      Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
      
  22. Alice Linux Google Chrome 46.0.2490.76 on

    感謝!但請問跑出來的總表資料不是完整的 是甚麼原因呢 (檢查各表格式有一樣)

    • Hi Alice,
      不好意思,這樣的資訊不足以研判耶
      可否請你提供連結讓蘋果米可以下載到你這份資料呢?

歡迎您發表迴響唷!