Autres Excel 2007 copier coller different , tableau sur un autre tableau

=SIERREUR(RECHERCHEH(COLONNE()-1;Feuil1!$A1:$T1;1;0);"")

Private Sub Worksheet_Activate()
Dim dest As Range, nlig&, ncol%
Application.ScreenUpdating = False
Cells.Delete 'RAZ
Set dest = [B2] 'à adapter
With Sheets("Feuil1").[A1].CurrentRegion 'à adapter
    nlig = .Rows.Count
    ncol = Application.Max(.Cells)
    dest = 1: dest.Resize(, ncol).DataSeries 'numérotation
    dest(2).Resize(nlig, ncol) = "=IFERROR(HLOOKUP(COLUMN()-" & dest.Column - 1 & "," & .Rows(1).Address(0, 1, External:=True) & ",1,0),"""")"
    dest(2).Resize(nlig, ncol) = dest(2).Resize(nlig, ncol).Value 'supprime les formules, à mettre en commentaire pour les voir
End With
'---mise en forme---
With dest.Resize(nlig + 1, ncol)
    Range("A1", .Cells).ColumnWidth = 2.5
    .HorizontalAlignment = xlCenter
    .Borders.Weight = xlThin 'bordures
    .Rows(1).Interior.ColorIndex = 6 'jaune
End With
End Sub