Range dynamique dans une destination d'un Autofill

Bonjour à tous !

Je commence mes premiers exploits sur Excel/VBA et je m'expose à un soucis qui m'est encore sans réponse malgré mes recherches et tests.
Le code est très simple mais fait apparaître une erreur '1004' La méthode AutoFill de la classe Range a échouée.

Mon soucis concerne un fichier plus important dans lequel je cherche à étendre la formule d'une cellule à une plage.

Avec le fichier joint vous verrez que ce soucis se produit aussi sur une valeur fixe et n'est donc pas liée à ma formule.
L'étirage de la cellule fonctionne sur la même ligne, mais ne fonctionne pas en plage de cellule de plusieurs lignes.

Ma variable lastProd renseigne la dernière ligne sur laquelle étirer la cellule B2 (en l'occurrence). Et est en réalité "dynamique", mais fixée à 10 pour ce test.

Je vous ai reproduit ce petit soucis dans un fichier de test.

En voici le code VBA :

Sub test()
'Déclaration d'un entier    
    Dim lastProd As Integer
'Initialisation de l'entier
    lastProd = 10
'Travail sur la feuille de test
    Sheets("test").Activate
'Etirage de la cellule sur une plage => Ligne posant problème
    Range("B2").AutoFill Destination:=Range("B2:D" & lastProd)
End Sub

Et le fichier test :

Bonjour

Cela est résolu nativement depuis 19 ans par les tableaux structurés

Bonjour 78chris,

Merci pour ta réponse rapide.
Je suis complètement d'accord avec ce que tu avances.

J'expose ici ce problème parce que mon besoin va au delà d'un simple tableau structuré.
Je récupère un fichier qui ne m'appartient pas et pour lequel je ne peux pas modifier la disposition ou les formats.
Je dois penser à l'utilisateur final du fichier qui pourrait être amené à "mal" utiliser le fichier.
Par conséquent, un bouton de calcul me permet d'étendre et calculer les champs concernés pour résoudre ces potentiels problèmes utilisateurs.

Toutes ces contraintes (liste non exhaustive) me poussent à trouver une solution à ce problème.

Je ne sais pas si les informations/justifications que j'apporte sont suffisantes, mais au delà d'une autre manière de fonctionner, je cherche à comprendre et résoudre un soucis qui pour l'instant reste sans réponse.

J'ai résolu mon problème en n'utilisant pas d'autofill.
Je n'avais pas pensé à appliquer la formule (la valeur) directement dans la plage de données

Sub test(lastProd, ldeb)
'Extension de la répartition pondérée
    Dim plage As Range
    Set plage = Range("EE8:IV" & lastProd)
'Pour la forme, sans la formule qui n'a pas d'intérêt ici
    plage.Formula = "UNE FORMULE"
End Sub