Sub MergeSpecificSheetsToSeparateTabs()
    Dim folderPath As String
    Dim sourceWorkbook As Workbook
    Dim mergedWorkbook As Workbook
    Dim ws As Worksheet
    Dim sheetName As String
    Dim fileName As String
    Dim newSheet As Worksheet
    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
    
    ' Set the folder path
    folderPath = "D:\Files\" ' You can modify this to your folder path
    
    ' Create a new workbook to hold the merged sheets
    Set mergedWorkbook = Workbooks.Add
    mergedWorkbook.Sheets(1).Name = "Temp" ' Temporary sheet to delete later
    
    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
            
            ' Add a new sheet to the merged workbook
            Set newSheet = mergedWorkbook.Sheets.Add(After:=mergedWorkbook.Sheets(mergedWorkbook.Sheets.Count))
            newSheet.Name = Left(sourceWorkbook.Name, 31) ' Sheet names are limited to 31 characters
            
            ' Copy the content of the specific sheet from the source workbook to the new sheet
            ws.UsedRange.Copy Destination:=newSheet.Cells(1, 1)
        End If
        
        sourceWorkbook.Close False
        fileName = Dir
    Loop
    
    ' Delete the temporary sheet if at least one sheet was found
    If sheetFound Then
        Application.DisplayAlerts = False
        mergedWorkbook.Sheets("Temp").Delete
        Application.DisplayAlerts = True
    Else
        MsgBox "This sheet is not found in any file.", vbExclamation
        mergedWorkbook.Close False
        Exit Sub
    End If
    
    ' Save the merged workbook
    mergedFilePath = folderPath & "MergedWorkbook_" & sheetName & ".xlsx"
    mergedWorkbook.SaveAs mergedFilePath
    mergedWorkbook.Close
    
    ' Open the merged workbook
    Workbooks.Open mergedFilePath
    
    MsgBox "All '" & sheetName & "' sheets from all files have been merged into separate tabs.", vbInformation
End Sub