recuperer la colonne (1 : 100 ) de chaque feuil de mon classeur

voila j'ai reussi a mettre un code mais ca marche que pour le nom qui sera enregistree dans O2
sachant que je veux le faire pour tt les noms qui se trouveront dans la colonne o (o2 jusquao 12)

voici le code




Sub essai_qpdc()
Dim a As String




a = Worksheets("NEW_VB_config").Range("o" & 2)


For i = 2 To 100

If Worksheets(a).Range("n" & i) <> "" Then

x = Left(Worksheets(a).Range("n" & i), 1)
y = Left(Worksheets(a).Range("n" & i), 2)
y1 = Right(y, 1)
y2 = Left(Worksheets(a).Range("n" & i), 3)
y22 = Right(y2, 1)
y3 = Right(Worksheets(a).Range("n" & i), 1)

If Worksheets(a).Range("a" & i) = Range("a1") Then

If x = 1 Then
Range("f" & i) = x
Range("b2") = Application.sum(Range("f2:f10000"))
ElseIf x = 2 Then
Range("g" & i) = x
Range("b3") = Application.sum(Range("g2:g10000")) / 2
ElseIf x = 3 Then
Range("h" & i) = x
Range("b4") = Application.sum(Range("h2:h10000")) / 3
ElseIf x = 4 Then
Range("i" & i) = x
Range("b5") = Application.sum(Range("i2:i10000")) / 4
End If

If y1 = 1 Then
Range("j" & i) = y1
Range("c2") = Application.sum(Range("j2:j10000"))
ElseIf y1 = 2 Then
Range("k" & i) = y1
Range("c3") = Application.sum(Range("k2:k10000")) / 2
ElseIf y1 = 3 Then
Range("l" & i) = y1
Range("c4") = Application.sum(Range("l2:l10000")) / 3
ElseIf y1 = 4 Then
Range("m" & i) = y1
Range("c5") = Application.sum(Range("m2:m10000")) / 4
End If


If y22 = 1 Then
Range("n" & i) = y22
Range("d2") = Application.sum(Range("n2:n10000"))
ElseIf y22 = 2 Then
Range("o" & i) = y22
Range("d3") = Application.sum(Range("o2😱10000")) / 2
ElseIf y22 = 3 Then
Range("p" & i) = y22
Range("d4") = Application.sum(Range("p2😛10000")) / 3
ElseIf y22 = 4 Then
Range("q" & i) = y22
Range("d5") = Application.sum(Range("q2:q10000")) / 4
End If

If y3 = 1 Then
Range("r" & i) = y3
Range("e2") = Application.sum(Range("r2:r10000"))
ElseIf y3 = 2 Then
Range("s" & i) = y3
Range("e3") = Application.sum(Range("s2:s10000")) / 2
ElseIf y3 = 3 Then
Range("t" & i) = y3
Range("e4") = Application.sum(Range("t2:t10000")) / 3
ElseIf y3 = 4 Then
Range("u" & i) = y3
Range("e5") = Application.sum(Range("u2:u10000")) / 4
End If

End If
End If

Next


End Sub