
Isaac Houreh
New Member
Members

Forum Posts: 1
Member Since:
July 9, 2022
July 9, 2022

Hi everyone,
I found this code online to combine multiple Excel files (100 files) into one as separate Spreadsheets, and it works just fine. However, in the destination file, the Spreadsheet names are general (Sheet(1), Sheet(2), etc.); I need to have the file names as Spreadsheet names in the combined file to be able to recognize them in the new file. Can someone please help me to update this code to serve that purpose?
Sub CombineMultipleFiles()
On Error GoTo eh
'declare variables to hold the objects required
Dim wbDestination As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wb As Workbook
Dim sh As Worksheet
Dim strSheetName As String
Dim strDestName As String
'turn off the screen updating to speed things up
Application.ScreenUpdating = False 'first create new destination workbook
Set wbDestination = Workbooks.Add 'get the name of the new workbook so you exclude it from the loop below
strDestName = wbDestination.Name 'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook
For Each wb In Application.Workbooks
If wb.Name < > strDestName And wb.Name < > "PERSONAL.XLSB" Then
Set wbSource = wb
For Each sh In wbSource.Worksheets
sh.Copy After: = Workbooks(strDestName).Sheets(1)
Next sh
End If
Next wb
'now close all the open files except the new file and the Personal macro workbook.
For Each wb In Application.Workbooks
If wb.Name < > strDestName And wb.Name < > "PERSONAL.XLSB" Then
wb.Close False
End If
Next wb
'remove sheet one from the destination workbook
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True 'clean up the objects to release the memory
Set wbDestination = Nothing
Set wbSource = Nothing
Set wsSource = Nothing
Set wb = Nothing 'turn on the screen updating when complete
Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub
Thanks,
Isaac

Catalin Bombea
Iasi, Romania
Admin

Forum Posts: 1844
Member Since:
November 8, 2013
November 8, 2013

Hi Isaac,
Instead of:
sh.Copy After: = Workbooks(strDestName).Sheets(1)
Use:
sh.Copy After: = Workbooks(strDestName).Worksheets.Count 'depending on the the user settings, a new wb might not always have 1 sheet Rename the last one:
Workbooks(strDestName).Worksheets(Workbooks(strDestName).Worksheets.Count).Name=sh.Name 'name of source sheet, change as needed. Make sure the name is unique and less than 30 chars
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Tracy English, David Birch, QSolutions Group
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Jennifer Rodriguez-Avila
Khaled Ibrahim
Kiran Supekar
Lisa Myers
Ronald White
Ginette Guevremont
Taryn Ambrosi
Mark Davenport
Christy Nichols
Harald Endres
Forum Stats:
Groups: 3
Forums: 24
Topics: 6530
Posts: 28602
Member Stats:
Guest Posters: 49
Members: 32820
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —
