彰化一整天的論壇

 找回密碼
 立即註冊
查看: 377|回復: 0

【轉貼】合併所有工作表

[複製鏈接]
發表於 2018-2-20 08:50:50 | 顯示全部樓層 |閱讀模式
The following VBA code can help you to get data from all worksheets of active workbook together into a new single worksheet. At the same time, all of the worksheets must have the same field structure, same column headings and same column order. Please do as follows:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.
  1. Sub Combine()
  2. Dim J As Integer
  3. On Error Resume Next
  4. Sheets(1).Select
  5. Worksheets.Add
  6. Sheets(1).Name = "Combined"
  7. Sheets(2).Activate
  8. Range("A1").EntireRow.Select
  9. Selection.Copy Destination:=Sheets(1).Range("A1")
  10. For J = 2 To Sheets.Count
  11. Sheets(J).Activate
  12. Range("A1").Select
  13. Selection.CurrentRegion.Select
  14. Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
  15. Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
  16. Next
  17. End Sub
複製代碼
資料來源: https://www.extendoffice.com/doc ... heets-into-one.html
回復

使用道具 舉報

您需要登錄後才可以回帖 登錄 | 立即註冊

本版積分規則

 ㄚ母滴雞湯
 員林香純滴雞精

Archiver|手機版|小黑屋|彰化一整天的論壇(Excel,Office)  |网站地图

GMT+8, 2019-8-24 01:39 , Processed in 0.165220 second(s), 19 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表