VBA: Combine data from Multiple Workbooks Into One File and Consolidate into one Tab

[unknownymous]

Hello Gurus,

Good day!

Could you possibly help me tweak below code? I have multiple workbooks and need to combine into one.

I have below code that can do it. However, I am getting an error message whenever there is same tab name that exists on different workbook. Note that the tab name most of the time maximize the number of letters applicable for tab names.

Example Tab Name:
Workbook 1 - ABDC0123456789_Subject123456781
Workbook 2 - ABDC0123456789_Subject123456781

Is it possible to shorten the tab name until 25 letters only so it will look like: First tab is ABDC0123456789_Subject123 then next is ABDC0123456789_Subject123 (2) and so on.

= = = = = = = = = = = =

Sub ConsolidateFiles()

Dim f, fpath As String
fpath = GetFolder & "\"
Application.ScreenUpdating = False
f = Dir(fpath)

Do While Len(f) > 0
Select Case Right(f, Len(f) - InStrRev(f, "."))
Case "xls", "xlsx", "csv"
OpenFile (fpath & f)
End Select
f = Dir
Loop

Sheets("Main").Activate

End Sub

Function GetFolder() As String
Dim f As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path
If .Show <> -1 Then GoTo There
f = .SelectedItems(1)
End With
There:
GetFolder = f
End Function

Private Sub OpenFile(filepath)
Dim sh, wb As Workbook, wbMe As Workbook
Set wbMe = ActiveWorkbook
Set wb = Workbooks.Open(filepath)
For Each sh In wb.Sheets
sh.Copy After:=wbMe.Sheets(wbMe.Sheets.Count)
Next sh
wb.Close False
End Sub

= = = = = = = = = = = =

Any thoughts will be much appreciated.

 

[Herakles]

Anything is possible.

The sheet name should clearly indicate what data is contained in the sheet.

Is there a meaning to all of the characters and their relative positions in the ABDC0123456789_Subject123 string?

Work out a convention and then determine how to convert the sheet name from what it is now to what it needs to be in the future.
Once you have done this you can develop a piece of code to apply the conversion routine. I can help in this respect.

It is not a good idea to use the suffix (2) for example. It gives off the message that the sheets contain duplicate data which they may not.
Excel does this but it does not know what else to do.

 

[unknownymous]

I

Anything is possible.

The sheet name should clearly indicate what data is contained in the sheet.

Is there a meaning to all of the characters and their relative positions in the ABDC0123456789_Subject123 string?

Work out a convention and then determine how to convert the sheet name from what it is now to what it needs to be in the future.
Once you have done this you can develop a piece of code to apply the conversion routine. I can help in this respect.

It is not a good idea to use the suffix (2) for example. It gives off the message that the sheets contain duplicate data which they may not.
Excel does this but it does not know what else to do.

I see. Do you have any recommendation? The workbook name and enclosed data is different from one another but the tab name is same for some workbook. I always get an error saying "the name is already taken" so it won't push through.

 

[Herakles]

If the sheet name is the same as an existing sheet name in the target workbook then Excel should use the (x) suffix to maintain the unique sheet name rule.

So... Copy the sheet and then it becomes the active sheet in the new workbook.

Just rename the active sheet as ni the sample VBA code below.

It's the sheet naming convention which is the key to this rather than the mechanics.

Workbooks("CopyFromWorkbook.xlsm").Sheets("Data 3").Copy After:=Workbooks("CopyToWorkbook.xlsm").Sheets(Workbooks("CopyToWorkbook.xlsm").Sheets.Count)

    ActiveSheet.Name = "NewWorksheetName"

 

[unknownymous]

Hello Herakles. Appreciate your help on this but a small favor, can you help me integrate the additional code on the existing one that I have as stated above.