Loading editor... if the number of tables is not too large, you can try this method. Open the total table (the table to be pasted), open a table to be copied, right-click the worksheet tag to be copied, and select move or copy a worksheet, create a copy and select the table to be moved to (the total table to be pasted ). This method is feasible for merging to the same file.
However, you cannot summarize the information in a table. You can use "=" to create a link (use a relative address). However, this method can only be used if the table structure is the same or similar, the file name and table name must be regular. This will soon be summarized into a table. The following job is to sort out the work. If you want data, you can change the link to the data. This is a good way to dynamically update your data.
WriteCodeYes, but if the workload is heavy, consider it!
Create a worksheet, name it, and save it to the same file folder as the merged 100 files. press Alt + F11 and double-click sheet1 (sheet1) in the project resource manager ), paste the following code in the code area on the right. Run. Wait for a while and then OK.
Sub Merge all worksheets of all workbooks in the current directory ()
Dim Mypath, myname, awbname
Dim WB As Workbook, wBN As String
Dim G As Long
Dim Num As Long
Dim Box As String
Application. screenupdating = False
Mypath = Activeworkbook. Path
Myname = Dir (mypath & " \ " & " *. Xls " )
Awbname = Activeworkbook. Name
Num = 0
Do While Myname <> ""
If Myname <> Awbname Then
Set WB = Workbooks. Open (mypath & " \ " & Myname)
Num = Num + 1
With Workbooks ( 1 ). Activesheet
. Cells (. Range ( " A65536 " ). End (xlup). Row + 2 , 1 ) = Left (Myname, Len (Myname) - 4 )
For G = 1 To Sheets. Count
WB. Sheets (g). usedrange. Copy. cells (. Range ( " A65536 " ). End (xlup). Row + 1 , 1 )
Next
WBN = WBN & CHR ( 13 ) & WB. Name
WB. Close False
End With
End If
Myname = Dir
Loop
Range ( " A1 " ). Select
Application. screenupdating = True
Msgbox " Merged " & Num & " All worksheets in the workbook. As follows: " & CHR ( 13 ) & WBN, vbinformation, " Prompt "
End sub