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

60

最近常常在進行手動的 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.

60 篇迴響

  1. 蜂蜜 Windows other version Google Chrome 56.0.2924.87 on

    請問大大

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

    如何讓其他表格同步?

  2. 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 : 因為不複製首行(所以做了位移)

  3. NANA Windows other version Google Chrome 56.0.2924.87 on

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

  4. NANA Windows other version Google Chrome 56.0.2924.87 on

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

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

  5. Daniel Mac OS X Safari 10.0 on

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

  6. Hi Jason 翰 ,

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

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

  7. Hi Jason 翰 ,

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

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

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

  9. Penny Windows other version Google Chrome 52.0.2743.116 on

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

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

  10. 您好! 謝謝您的貢獻與分享,但我輸入後,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私訊功能提供檔案喔

  11. Andrew Windows other version Unknow on

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

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

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

  13. Sibyl Mac OS X Safari 9.0 on

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

  14. Sibyl Mac OS X Safari 9.0 on

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

  15. Sibyl Mac OS X Safari 9.0 on

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

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

  16. Sibyl Mac OS X Safari 9.0 on

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

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

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

  19. 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
      
  20. 不好意思,小弟是個新手,正在網上爬文學習

    而最近有個需求剛好跟這段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)
      
  21. Alice Linux Google Chrome 46.0.2490.76 on

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

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

歡迎您發表迴響唷!