Private Sub Worksheet_Activate()
Dim dercel As Range, h&, source As Range, dest As Range, i As Byte
Application.ScreenUpdating = False
'---transfert depuis la feuille Heures---
With Sheets("Heures")
Set dercel = .[B:B].Find("MOI", , xlValues, xlWhole)
h = IIf(dercel Is Nothing, 10000, dercel.Row - 11)
Set source = .[A11,B11,H11,L11,Q11,T11,V11]
Set dest = [A11,B11,D11,M11,Q11,U11,AB11]
For i = 1 To source.Areas.Count
source.Areas(i).Resize(h).Copy dest.Areas(i) 'copie tout
dest.Areas(i).Resize(h) = source.Areas(i).Resize(h).Value 'copie les valeurs
dest.Areas(i)(h + 1).Resize(Rows.Count - h - 10).Delete xlUp 'RAZ en dessous
Next
End With
'---transfert depuis la feuille Ciment---
With Sheets("Ciment")
Set dercel = .[B:B].Find("INDIRECTS", , xlValues, xlWhole)
h = IIf(dercel Is Nothing, 10000, dercel.Row - 11)
Set source = .[H11,K11,L11,Q11,T11,V11]
Set dest = [E11,I11,N11,R11,V11,AC11]
For i = 1 To source.Areas.Count
source.Areas(i).Resize(h).Copy dest.Areas(i) 'copie tout
dest.Areas(i).Resize(h) = source.Areas(i).Resize(h).Value 'copie les valeurs
dest.Areas(i)(h + 1).Resize(Rows.Count - h - 10).Delete xlUp 'RAZ en dessous
Next
End With
'---transfert depuis la feuille Acier---
With Sheets("Acier")
Set dercel = .[B:B].Find("INDIRECTS", , xlValues, xlWhole)
h = IIf(dercel Is Nothing, 10000, dercel.Row - 11)
Set source = .[H11,L11,Q11,T11,V11]
Set dest = [F11,O11,S11,W11,AD11]
For i = 1 To source.Areas.Count
source.Areas(i).Resize(h).Copy dest.Areas(i) 'copie tout
dest.Areas(i).Resize(h) = source.Areas(i).Resize(h).Value 'copie les valeurs
dest.Areas(i)(h + 1).Resize(Rows.Count - h - 10).Delete xlUp 'RAZ en dessous
Next
End With
End Sub