EXCEL VBA To Consolidate Multiple Workbook

[jgaureo]

Hi All,

I found a good macro in internet and it almost suit on my needs, however I can't seem to modify it with the results I need.

Public Sub consolWB()    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = Range("C2").Value
    Set folder = FSO.GetFolder(folderPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
        
    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
            Set openWB = Workbooks.Open(wb)
            For Each ws In openWB.Worksheets
                ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Next ws
            openWB.Close
        End If
    Next wb
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

I need to qualify if the source workbook has sheet name or none, if none i.e. "Sheet1" or "Sheet2" etc. the macro should use the file name instead. Keep the workbook sheetname if present. Also if possible to use opendialog instead of placing the forder path in C2.

Thanks in advance.

Brgds,
JAPHZ