how to merge different Excel files into one single Excel sheet 多薄多表合并

If you have multiple Excel sheets which have the same title line, then you can merge them into one single sheet with this method:

open one excel sheet,
ALT+F11, there will be a blank text editor,
Paste the following code:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3 
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

change the folder path of where you files in
press “F5” or click on play icon to run the code (RunSub). You’ll see working progress on left sheet menu.

via:
http://www.oaultimate.com/office/merge-multiple-excel-files-into-a-single-spreadsheet-ms-excel-2007.html

主頁

Terms of Use

Privacy Policy

Bianca Censori的新衣服

內衣模特heather-crook

namesilo域名註冊優惠碼:1d0llar,註冊域名的時候填寫這個優惠碼可以便宜1美元。

超級方便的小工具

Windows電腦一鍵關機與睡眠 - 桌面捷徑教學

Windows電腦的休眠與睡眠的差別

很有料的跨設備文件传送工具

webwormhole,端對端加密,實時傳送,不限制文件的大小,文件甚至不經過上傳到伺服器的環節,超贊的

wormhole,文件最大可達 10 GB

或許這世間一切的好,往往都是求之不得的吧。有心栽花花不發,無心插柳柳成蔭;有心栽柳柳不成蔭,無意種花花卻繁華