Extraire des données en vue tableau

Bonjour le forum
J’ai réalisé une application pour le travail, mais c’est une vraie usine à gaz.
Il s’agit d’extraire des données de deux colonnes (C et F) en vue de les transposer en un tableau récapitulatif.
J’extrais toutes les valeurs une à une, je les colle dans une ligne puis les transpose
Et donc je suis parti comme ceci

valtest = ""
'*******BRUIT
For i = 2 To NbFeuil
With Sheets(i)
For Each c In .Range("C15:C17")
If c <> valtest Then
.Range(.Cells(Maligne, 4), .Cells(Maligne, 4)) = "BruitT"
End If
Next
End With
Next i
'************Poussieres
For i = 2 To NbFeuil
With Sheets(i)
For Each c In .Range("C33:C40")
If c <> valtest Then
.Range(.Cells(Maligne, 5), .Cells(Maligne, 5)) = "Poussieres"
End If
Next
End With
Next i

Et ainsi de suite pour tous les risques … et c’est une usine à gaz.
puis construction du tableau

'*******Recueil d'infos (boucle sur les feuilles)
'**dans chaque feuille, (les infos sont stockées sur la ligne 90)

For Each sh In ActiveWorkbook.Sheets
If sh.name <> ThisWorkbook.name Then
sh.Activate

Range(Cells(Maligne, 1), Cells(Maligne, 50)).Copy 'copie ligne"90"
With Sheets(1)
.Cells(sh.Index + 8, 2).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
.Cells(sh.Index + 8, 2) = sh.name 'copie des noms
End With
End If
Next

Je viens auprès de vous pour me donner le canevas de départ pour faire ce tableau afin de me simplifier ces lignes (je me bagarre avec les Ubound et rien ne marche)

La feuille 1 est la feuille de départ une feuille par personne
La feuille 2 est le résultat de mon travail
Merci pour votre aide
Francis