Search sheet and display results

Do you mean you want sheet2 to revert to "Sheet2"? If so, try this:

Private Sub CommandButton1_Click()
    
    Dim ManufacturerNum
    Dim Details As Double
    ManufacturerNum = InputBox("Enter the manufacturer")
    If ManufacturerNum = Cancel Then
        Sheet2.Name = "Sheet2"
        Exit Sub
    End If
    With Sheets("List")
        Sheet2.UsedRange.Clear
        .AutoFilterMode = False
        .Range("A1").AutoFilter field:=1, Criteria1:=ManufacturerNum
        .AutoFilter.Range.Copy Sheet2.Range("A1")
        .AutoFilterMode = False
    End With
    Sheet2.Name = ManufacturerNum
    
End Sub

Problem solved! Thanks for all your help mate