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

93

最近常常在進行手動的 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⌋ 的新工作表了喔
Share.

93 篇迴響

  1. 泥可 Windows other version Unknow on

    謝謝你快速的回覆喔,原始資料是一張表格我只要第四欄到第13欄位的值。
    我使用下方程式碼,可是還是一樣表頭跟表尾出來了,想確認哪裡有問題…謝謝你囉

    ‘將 Excel活頁簿中的多個工作表合併為一個
    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 = 4 To 13
    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

  2. 泥可 Windows other version Unknow on

    請問喔,合併資料有成功,可是為什麼欄位也跟著複製了,要怎麼解決呢。

    • 泥可 Windows other version Unknow on

      對不起,我描述清楚一點:我要的值要從A4到A13,目前產出會把表頭欄位跟表尾資料帶出

    • Hi 泥可,

      請把下面這行
      For J = 2 To Sheets.Count
      換成
      For J = 4 To 13
      即可

      請再試試看喔 ^^

  3. Hi Jason,

    如何將下列動作選擇部分或全部工作表,請指教

    Sub unhiderowsandcolumns()

    Rows.Hidden = False

    Columns.Hidden = False

    Range(“A5”).Select
    With ActiveWindow
    ActiveSheet.ShowAllData

    End With

    End Sub

  4. Emma Windows other version Google Chrome 66.0.3359.181 on

    Hi 你好,
    想了解這個功能的程式碼,只是我在上面的網誌內容中並沒有看到程式碼內容
    請問哪裡可以參考呢?

  5. winnie Windows other version Google Chrome 66.0.3359.117 on

    謝謝您的回應,後來我發現到是 Combined工作表中的資料有少缺,不是樞紐分析表的問題, 是少了一個工作表,比如我有A、B、C表格,合併後只有A+C沒有B,請問是甚麼原因? 麻煩您了,謝謝

  6. winnie Windows other version Google Chrome 66.0.3359.117 on

    您好,謝謝您的分享,想請教您我使用您的方法有成功合併出工作表Combined,又再針對Combined工作表使用樞紐分析,但分析出來的資料有少缺,可在Combined工作表中確實有該筆資料,試著重複做過幾次還是一樣,我是個新手,不知道是哪個環節錯了? 麻煩您了,謝謝。

    • Hi Winnie,
      EXCEL的樞鈕分析功能,蘋果米沒有研究耶
      這次還真的沒法子幫上您了,不好意思

  7. Eason Windows other version Unknow on

    Hi J大:

    謝謝回覆
    所以,修改成下面這樣就可以了嗎??因為就是那一段,我不太了解

    修改前:
    For J = 2 To 3
    Sheets(J).Activate
    Range(“A1”).Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(ActiveSheet.UsedRange.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
    修改後:
    For J = 2 To 3
    Sheets(J).Activate
    Range(“A1”).Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count – 1).Select
    Selection.Copy
    Sheets(1).Range(“A65536”).End(xlUp)(2).PasteSpecial xlPasteValues

  8. Eason Windows other version Unknow on

    Hi ~謝謝大大的分享,但有問題想詢問一下。
    假設我要合併的資料是Sheets(1)與Sheets(2),但Sheets(1)與Sheets(2)的資料是Sheets(3)與Sheets(4)用複製過去的,(例如(Sheets(1)的欄位A2要秀出Sheets(3)欄位B2的數值,A2=Sheets(3)!$B2 ) 在合併時,有辦法只要合併值就好嗎??
    因為合併時,Sheets(1)的資料有跑出來,但是Sheets(2)的資料卻是有成功複製到欄位數,但是內容的資料卻無資料,請問這有辦法解決嗎?
    感謝大大

    • Hi Eason,
      你要改用我註解起來的程式碼進複製才會把得到的值一起複製過去
      ‘只需要複製欄位值,請註解掉上一行程式碼,並將下列二行的註解拿掉
      ‘Selection.Copy
      ‘Sheets(1).Range(“A65536”).End(xlUp)(2).PasteSpecial xlPasteValues

  9. Cher Windows other version Google Chrome 62.0.3202.94 on

    您好~ 請問在按下F5之後 會跳出一個更新數值的視窗並叫我選取檔案,
    請問是哪裡出了問題呢? 謝謝!

  10. 小雪 Windows other version Unknow on

    HI JASON 翰:
    我是VBA新手,想請問若想從SHEET(更新名單)篩選特定資料將特定欄位,貼製SHEET(2)資料,但SHEET(更新名單)會有十種篩選特定人員名單,貼製SHEET(2)時貼完每一種人員名單最後要加上一列(黃色),要怎麼處理?

    • Hi 小雪,
      如果你想達到的目的是看各區的人員總數
      那可以不用寫 VBA 喔
      直接利用 Excel 提供的 小計 功能就能達成

  11. Avon Windows other version Unknow on

    hi 您好:
    我的sheets共有15個,但Combined合併只合併9個sheets,有6個sheets沒有再Combined,查不出原因,可以幫忙看一下嗎?

    • Hi 昌賢,

      理論上應該是可以做到的
      你可以試著改改看, 如果成功了
      再回來分享一下

  12. Jason您好:
    謝謝您即時的回答!
    想詢問是指現在這支程式碼無法達到需求,還是指整個excel無法這樣操作呢?

  13. 您好:
    想詢問是否能依各分頁類別的不同,各別輸出特定欄位到指定位置呢?

    例如:
    將sheet1的B1:B200 複製到 Combined 的A1:A200
    將sheet2的E1:E200 複製到 Combined 的A201:A400

    因為報表欄位很亂,想知道若有此需求該如何處理,感恩您!

    • Hi Huang Tzu-Ting,
      目前的程式版本並無法支援你的需求
      但你的需求是有機會被實作出來的

  14. YAYA Windows other version Google Chrome 59.0.3071.115 on

    感謝提供這個VBA~
    我這個檔案裡面有3個工作表
    貼上語法後,只合併前2個工作表???
    能否幫忙看看問題出在哪裡~
    該怎麼寄檔案給你呢?

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

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

  16. 蜂蜜 Windows other version Google Chrome 56.0.2924.87 on

    請問大大

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

    如何讓其他表格同步?

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

  18. NANA Windows other version Google Chrome 56.0.2924.87 on

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

  19. NANA Windows other version Google Chrome 56.0.2924.87 on

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

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

  20. Daniel Mac OS X Safari 10.0 on

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

  21. Hi Jason 翰 ,

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

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

  22. Hi Jason 翰 ,

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

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

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

  24. Penny Windows other version Google Chrome 52.0.2743.116 on

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

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

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

  26. Andrew Windows other version Unknow on

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

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

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

  28. Sibyl Mac OS X Safari 9.0 on

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

  29. Sibyl Mac OS X Safari 9.0 on

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

  30. Sibyl Mac OS X Safari 9.0 on

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

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

  31. Sibyl Mac OS X Safari 9.0 on

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

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

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

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

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

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

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

歡迎您發表迴響唷!

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料