Sub MergeSpecificSheetsFromFiles()
    Dim folderPath As String
    Dim mergedWorkbook As Workbook
    Dim sourceWorkbook As Workbook
    Dim ws As Worksheet
    Dim fileName As String
    Dim mergedSheet As Worksheet
    Dim lastRow As Long
    Dim sheetName As String
    Dim sheetFound As Boolean
    Dim mergedFilePath As String
    
    ' Prompt user to enter the sheet name
    sheetName = InputBox("Enter the sheet name you want to merge:", "Sheet Name")
    If sheetName = "" Then
        MsgBox "No sheet name entered. Exiting...", vbExclamation
        Exit Sub
    End If
    
    folderPath = "D:\Files\"
    Set mergedWorkbook = Workbooks.Add
    Set mergedSheet = mergedWorkbook.Sheets(1)
    
    fileName = Dir(folderPath & "*.xls*") ' Handles both .xls and .xlsx extensions
    sheetFound = False
    
    Do While fileName <> ""
        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)
        On Error Resume Next
        Set ws = sourceWorkbook.Sheets(sheetName)
        On Error GoTo 0
        
        If Not ws Is Nothing Then
            sheetFound = True
            lastRow = mergedSheet.Cells(mergedSheet.Rows.Count, 1).End(xlUp).Row + 1
            mergedSheet.Cells(lastRow, 1).Resize(ws.UsedRange.Rows.Count, 1).Value = sourceWorkbook.Name
            
            ws.UsedRange.Copy Destination:=mergedSheet.Cells(lastRow, 2)
        End If
        
        sourceWorkbook.Close False
        fileName = Dir
    Loop
    
    If Not sheetFound Then
        MsgBox "This sheet is not found in any file.", vbExclamation
        mergedWorkbook.Close False
        Exit Sub
    End If
    
    mergedFilePath = folderPath & "MergedWorkbook_" & sheetName & ".xlsx"
    mergedWorkbook.SaveAs mergedFilePath
    mergedWorkbook.Close
    
    ' Open the merged workbook
    Workbooks.Open mergedFilePath
    
    MsgBox "All '" & sheetName & "' sheets from all files merged successfully", vbInformation
End Sub