onglet

Option Explicit
Sub test()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\doc\prod.xls")
With wb
If Not FeuilleExiste(wb, .Sheets("Feuil1").Range("G7").Value) Is Nothing Then Exit Sub
    .Worksheets.Add(after:=.Sheets(.Sheets.Count)).Name = .Sheets("test").Range("G7").Value
End With
End Sub
Function FeuilleExiste(wb As Workbook, f As String) As Worksheet
On Error Resume Next
Set FeuilleExiste = wb.Worksheets(f)
End Function

Workbooks.Open FileName:="classeur.xls"

Workbooks("classeur.xls").Activate

Sheets.Add
ActiveSheet.Name = "onglet"

Public Function FeuilleExiste(sNomFeuille As String) As Boolean
    On Error GoTo Err_FeuilleExiste
    FeuilleExiste = False
    FeuilleExiste = Not ActiveWorkbook.Worksheets(sNomFeuille) Is Nothing
Err_FeuilleExiste:
End Function

'Vérifie si la feuille onglet existe, si oui message d'erreur, si non creation
If FeuilleExiste("onglet") Then
    MsgBox "feuille onglet existe déjà"
Else
    Sheets.Add
    ActiveSheet.Name = "onglet"
End If

Dim wb As Workbook
Set wb = Workbooks.Open("C:\doc\prod.xls")
With wb
If Not FeuilleExiste(wb, ThisWorkbook.Sheets("Feuil1").Range("G7").Value) Is Nothing Then Exit Sub
    .Worksheets.Add(after:=.Sheets(.Sheets.Count)).Name = ThisWorkbook.Sheets("test").Range("G7").Value
End With