Re : ouvrir une page avec un format préétabli
Voici le code .
le gros du code n'est absolument pas intéressant c'est jsute le remplissage d'après une base de données.
Public Sub remplir()
Dim commune As Variant
Dim l As Integer
commune = InputBox("quelle est la commune pour laquelle vous souhaitez remplir la fiche?")
Worksheets("BDD").Activate
Dim nomcommune As Worksheet
'nomcommune.Name = commune
'nomcommune = Worksheets("fiche").Copy
For I = 5 To 15
If Cells(I, 1) = commune Then
l = I
End If
Next
'remplissage Equipements
Worksheets("fiche").Activate
Cells(4, 2) = commune
Cells(11, 2) = Worksheets("BDD").Cells(l, 2)
Cells(12, 2) = Worksheets("BDD").Cells(l, 3)
Cells(13, 2) = Worksheets("BDD").Cells(l, 4)
Cells(14, 2) = Worksheets("BDD").Cells(l, 5)
Cells(15, 2) = Worksheets("BDD").Cells(l, 6)
Cells(16, 2) = Worksheets("BDD").Cells(l, 7)
Cells(17, 2) = Worksheets("BDD").Cells(l, 8)
Cells(18, 2) = Worksheets("BDD").Cells(l, 11)
Cells(19, 2) = Worksheets("BDD").Cells(l, 12)
'VNC
Cells(10, 5) = Worksheets("BDD").Cells(4, 13)
Cells(11, 5) = Worksheets("BDD").Cells(l, 13)
'remplissage investissement
Cells(26, 3) = Worksheets("BDD").Cells(3, 9)
Cells(27, 3) = Worksheets("BDD").Cells(3, 10)
Cells(26, 4) = Worksheets("BDD").Cells(l, 9)
Cells(27, 4) = Worksheets("BDD").Cells(l, 10)
For I = 1 To 3
For j = 1 To 3
Cells(27 + I, 1 + j) = Worksheets("BDD").Cells(l, 13 + j + 3 * (I - 1))
Next j
Next I
'remplissage usagers
For I = 1 To 3
Cells(33, 1 + I) = Worksheets("BDD").Cells(l, 26 + I)
Cells(34, 1 + I) = Worksheets("BDD").Cells(l, 29 + I)
'remplissage consommation
Cells(38, 1 + I) = Worksheets("BDD").Cells(l, 32 + I)
Next I
For I = 1 To 6
Cells(44, 1 + I) = Worksheets("BDD").Cells(l, 35 + I)
Cells(45, 1 + I) = Worksheets("BDD").Cells(l, 41 + I)
Next I
For j = 1 To 4
For I = 1 To 6
Cells(46 + j, 1 + I) = Worksheets("BDD").Cells(l, 47 + 4 * (j - 1) + I)
Next I
Next j
For j = 1 To 6
For I = 1 To 6
Cells(51 + j, 1 + I) = Worksheets("BDD").Cells(l, 71 + 6 * (j - 1) + I)
Cells(58 + j, 1 + I) = Worksheets("BDD").Cells(l, 107 + 6 * (j - 1) + I)
Next I
Next j
'remplissage régie
Cells(70, 4) = Worksheets("BDD").Cells(l, 144)
Cells(71, 4) = Worksheets("BDD").Cells(l, 145)
'remplissage grille tarifaire
Cells(78, 4) = Worksheets("BDD").Cells(l, 146)
Cells(79, 4) = Worksheets("BDD").Cells(l, 147)
Cells(81, 4) = Worksheets("BDD").Cells(l, 148)
End Sub