Tente este codigo

Sub GerarFichas()

Dim W As Worksheet
Dim L As Long
Dim WF As Worksheet


Set W = Sheets("BANCO DE DADOS")
Set WF = Sheets("FICHA")
    L = 2
    
    Do While W.Cells(L, 1) <> ""
    
    With WF
    .Range("B6").Value = W.Cells(L, 1).Value
    .Range("B7").Value = W.Cells(L, 2).Value
    .Range("B8").Value = W.Cells(L, 3).Value
    .Range("B9").Value = W.Cells(L, 4).Value
    .Range("B10").Value = W.Cells(L, 5).Value
    .Copy After:=Sheets(Sheets.Count)
    End With
    ActiveSheet.Name = W.Cells(L, 2).Value
    L = L + 1
    
    Loop

   With WF
    .Select
    .Range("B6").Value = ""
    .Range("B7").Value = ""
    .Range("B8").Value = ""
    .Range("B9").Value = ""
    .Range("B10").Value = ""
    End With
     
End Sub