1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
| Sub Annuaire()
ligneSource = 2 ' ligne de départ
largeurSource = 7 ' largeur source (nb colonnes)
hpageDest = 150 ' hauteur page edition
ncolDest = 1 ' nb colonnes edition
ligneDest = 2 ' ligne de départ
'-------------------------------- création temp
Application.DisplayAlerts = False
On Error Resume Next
Sheets("temp").Delete
On Error GoTo 0
Sheets("BD").Copy after:=Sheets("BD")
ActiveSheet.Name = "temp"
Cells(ligneSource, 1).Select
Do While ActiveCell <> ""
Lettre = Left(ActiveCell, 1)
ActiveCell.EntireRow.Insert
ActiveCell = Lettre
ActiveCell.Font.Bold = True
ActiveCell.Font.ColorIndex = 3
Do While Left(ActiveCell, 1) = Lettre
ActiveCell = ActiveCell & String(58, ".")
ActiveCell.Offset(1, 0).Select
Loop
Loop
'--------------------------------- création Edition
Sheets("editionAnnuaire").ResetAllPageBreaks
Sheets("editionAnnuaire").Cells.ClearContents
For col = 1 To ncolDest ' en tête de colonnes
Sheets("temp").Cells(ligneSource - 1, 1).Resize(1, largeurSource).Copy _
Sheets("editionAnnuaire").Cells(1, (col - 1) * (largeurSource + 1) + 1)
Next col
'-
Do While Sheets("temp").Cells(ligneSource, 1) <> ""
For col = 1 To ncolDest
Cells(ligneSource, 1).Resize(hpageDest, largeurSource).Copy _
Sheets("editionAnnuaire").Cells(ligneDest, (col - 1) * (largeurSource + 1) + 1)
ligneSource = ligneSource + hpageDest
Next
Sheets("editionAnnuaire").HPageBreaks.Add Before:=Cells(ligneDest + hpageDest, 1)
ligneDest = ligneDest + hpageDest
Loop
Sheets("editionAnnuaire").Select
Sheets("editionAnnuaire").Cells.Font.Size = 9
'ActiveSheet.PrintPreview
End Sub |
Partager