Bonjour Rama,
Effectivement, le code VBA de ton EXCEL est protégé ce qui nous oblige à faire une copie dans un nouvel EXCEL.
Je te propose le code suivant :
Sub CopieColD()
Const cFirstRowD = 9
Const cFirstRowTo = 8
Dim oSheet As Excel.Worksheet
Dim oSheetTo As Excel.Worksheet
Dim oCell As Excel.Range
Dim oRange As Excel.Range
Dim lLastRow As Long, lRow As Long
Dim lCol As Long
'On fixe la colonne à "D"
lCol = 4
'On remplace les valeurs cellules vides de "D" avec 0
For Each oSheet In ThisWorkbook.Worksheets
If IsNumeric(Right(oSheet.Name, 4)) Then
lLastRow = oSheet.UsedRange.Rows.Count
Set oRange = oSheet.Range(oSheet.Cells(cFirstRowD, lCol), oSheet.Cells(lLastRow, lCol))
oRange.Replace "", "0"
End If
Next
'On recopie les colonnes "D" dans la feuille de récap en colonne "B"
Set oSheetTo = ThisWorkbook.Worksheets("EI30")
lRow = cFirstRowTo
For Each oSheet In ThisWorkbook.Worksheets
If IsNumeric(Right(oSheet.Name, 4)) Then
lLastRow = oSheet.UsedRange.Rows.Count
Set oRange = oSheet.Range(oSheet.Cells(cFirstRowD, lCol), oSheet.Cells(lLastRow, lCol))
oRange.Copy oSheetTo.Cells(lRow, 2)
lRow = oSheetTo.UsedRange.Rows.Count + 1
End If
Next
MsgBox "Recopie colonne 'D' terminée!", vbExclamation
Set oSheetTo = Nothing
End Sub