Merge Workbooks into tabs

Ask Question Forum:

Model Library:2025-02-08:A.I. model is online auto reply

Show

ASK RECENT

Merge Workbooks into tabs

Asked by Wass_QA

at 2024-07-17 17:29:45

Point:400 Replies:9 POST_ID:829012USER_ID:11914

Topic:

Microsoft Excel Spreadsheet Software;;

Hello,
Can you please help.
I'm trying to find a macro that would:

I have about 25 - 50 Excel Workbooks (same folder) that I need to merge into one workbook,
but, I need each Workbooks to be on a separate tab.

Workbooks 1
Workbooks 2
Workbooks 3....

so, I should have 25 tabs to 50 tabs. (Workbooks 1,Workbooks 2,Workbooks 3,...)

Any help is appreciated.
thanks

Expert: Glenn Ray replied at 2024-07-18 09:56:31

You're welcome.

Author: Wass_QA replied at 2024-07-18 09:27:31

Do you need to include comma-delimited (CSV) files along with Excel (xls) files? It would require an additional process.

If you want CSV files instead of XLS files, this code would work:

Sub Combine_CSV_Files()    Dim strMaster, strFile, strFileName, strFilePath As String    Application.ScreenUpdating = False    strMaster = Application.ActiveWorkbook.Name    strFileName = Application.GetOpenFilename("Comma Delimited Files (*.csv), *.csv")    If strFileName = "" Or strFileName = "False" Then Exit Sub    strFilePath = Left(strFileName, InStrRev(strFileName, ""))    strFile = Dir(strFilePath & "*.csv")    Do While strFile <> ""        Workbooks.Open Filename:=strFilePath & "" & strFile        Sheets(1).Copy After:=Workbooks(strMaster).Sheets(Workbooks(strMaster).Sheets.Count)        ActiveSheet.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)        Workbooks(strFile).Close SaveChanges:=False        strFile = Dir    Loop    Application.ScreenUpdating = True    Sheets("Main").Select    MsgBox "Process complete.", vbOKOnly, "Combine Excel Files"End Sub

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:

Select All Code

I've updated the example workbook with this second macro (and button).

-Glenn

Attachment:EE-CombineXL.xlsm

Accepted Solution

Expert: Glenn Ray replied at 2024-07-18 09:13:34

400 points EXCELLENT

Do you need to include comma-delimited (CSV) files along with Excel (xls) files? It would require an additional process.

If you want CSV files instead of XLS files, this code would work:

Sub Combine_CSV_Files()    Dim strMaster, strFile, strFileName, strFilePath As String    Application.ScreenUpdating = False    strMaster = Application.ActiveWorkbook.Name    strFileName = Application.GetOpenFilename("Comma Delimited Files (*.csv), *.csv")    If strFileName = "" Or strFileName = "False" Then Exit Sub    strFilePath = Left(strFileName, InStrRev(strFileName, ""))    strFile = Dir(strFilePath & "*.csv")    Do While strFile <> ""        Workbooks.Open Filename:=strFilePath & "" & strFile        Sheets(1).Copy After:=Workbooks(strMaster).Sheets(Workbooks(strMaster).Sheets.Count)        ActiveSheet.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)        Workbooks(strFile).Close SaveChanges:=False        strFile = Dir    Loop    Application.ScreenUpdating = True    Sheets("Main").Select    MsgBox "Process complete.", vbOKOnly, "Combine Excel Files"End Sub

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:

Select All Code

I've updated the example workbook with this second macro (and button).

-Glenn

400 points EXCELLENThttp://filedb.experts-exchange.com/incoming/2014/07_w29/861467/EE-CombineXL.xlsm,EE-CombineXL.xlsm,Author Closing Commentby: Wass_QAPosted on 2014-07-18 at 09:27:31

Author: Wass_QA replied at 2024-07-18 08:33:37

thank you very much Glen,
this worked,
how do I add .csv file formats?
thanks

Expert: Glenn Ray replied at 2024-07-18 08:06:41

The following code should do what you request:

Option ExplicitSub Combine_XL_Files()    Dim strMaster, strFile, strFileName, strFilePath As String    Application.ScreenUpdating = False    strMaster = Application.ActiveWorkbook.Name    strFileName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls;*.xlsx")    If strFileName = "" Or strFileName = "False" Then Exit Sub    strFilePath = Left(strFileName, InStrRev(strFileName, ""))    strFile = Dir(strFilePath & "*.xls*")    Do While strFile <> ""        Workbooks.Open Filename:=strFilePath & "" & strFile        Sheets(1).Copy After:=Workbooks(strMaster).Sheets(Workbooks(strMaster).Sheets.Count)        ActiveSheet.Name = Left(strFile, InStr(1, strFile, ".xls") - 1)        Workbooks(strFile).Close SaveChanges:=False        strFile = Dir    Loop    Application.ScreenUpdating = True    Sheets("Main").Select    MsgBox "Process complete.", vbOKOnly, "Combine Excel Files"End Sub

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:

Select All Code

It does not check for filename length, nor does it check for any duplicate filenames (possible if you have different Excel versions with the same name: ex., "File1.xls", "File1.xlsx", "File1.xlsm"). However, it should work given your described environment.

Also, it only copies the first sheet of any workbook. The assumption is that each of the workbooks are single-sheet workbooks and that the resulting sheet tab names will be renamed to the workbook filename.

I've attached a macro-enabled file for your use.

Regards,
-Glenn

Author: Wass_QA replied at 2024-07-18 07:19:31

Hello,
They are not sequential,
the file names are less than 30 Characters.

Example
Mark 377
Route 3810
Dennis 340
Run 700

Thank you

Expert: C. Blaise Mitsutama replied at 2024-07-18 06:36:47

Wass_QA,

Are the filenames of the 25-50 files sequential (e.g., workbook1, workbook2, etc.) OR do the filenames remain the same (e.g., permafilename, alwaysthesamename, etc.) OR could the filenames change arbitrarily?

Will any of the filenames be longer than 31 characters (tab label limit) or will the first 31 characters of any filenames be the same?

Author: Wass_QA replied at 2024-07-18 06:13:40

Hi Duncan,
I have 25-50 Workbooks. (or more)

Expert: duncanb7 replied at 2024-07-18 00:30:10

Could you turn on macro Recording on develope tab on Excel 2007 or tools->marco on Excel 2003. After it enable, You can do what you want manually on the Excel sheet, and then open VBA to view those macro code that recorded all what you did on what you want before such as opening another Excel file, adding tab or adding chart, or drag the cell formual, etc....

Duncan

新聞財經娛樂♬資訊 News Economy

About Us|Service|Pricing

&nbps;

News即時新聞:↓

News即時新聞:↑

On Balance Volume (OBV):

Youtube Video♬ Sharing by members:↓

Youtube Video♬ Sharing by members:↑

Ask Question Forum:

Merge Workbooks into tabs

Your Information

Members/Profolio