[Original] How to combine worksheets of the same format in multiple workbooks into one Worksheet

How to merge worksheets of the same format in multiple workbooks into one Worksheet

Sub books2sheets () 'defines the Variable Application in the dialog box. screenupdating = false dim FD as filedialog set FD = application. filedialog (msofiledialogfilepicker) 'creates a new workbook dim newwb as workbook set newwb = workbooks. add with FD if. show =-1 then' defines a single file variable dim vrtselecteditem as variant 'defines the cycle variable dim I as integer I = 1' to start File Retrieval for each vrtselecteditem in. selecteditems 'Open the merged workbook dim tempwb as workbook set tempwb = workboo KS. open (vrtselecteditem) 'Copy the worksheet tempwb. worksheets (1 ). copy before: = newwb. worksheets (I) 'change the worksheet name of the new workbook to the file name of the copied workbook, which is applied to the xls file, that is, the file of the Excel97-2003. If it is excel2007, it needs to be changed to XLSX newwb. worksheets (I ). name = VBA. replace (tempwb. name ,". xls "," ") 'Close the merged workbook tempwb. close savechanges: = false I = I + 1 next vrtselecteditem end if end with set FD = nothing sheets ("sheet1 "). select sheets ("sheet1 "). name = "summary" msgbox "now has a workbook The sheet table in is merged into a workbook. Now, the worksheet in the same format is merged into a sheet table "sheet (" summary "). select call nsheetsto1sheet application. screenupdating = true end sub nsheetsto1sheet () application. screenupdating = false for j = 1 to sheets. count if sheets (j ). name <> activesheet. name then x = range ("a65536 "). end (xlup ). row + 1 Sheets (j ). usedrange. copy cells (x, 1) end if next range ("B1 "). select application. screenupdating = true msgbo X "All worksheets in the current workbook have been merged! ", Vbinformation," prompt "End sub