Discussion :

[baba77290]

Bonjour,

Je souhaiterais de l'aide pour un problème sur lequel j'ai passé beaucoup de temps mais n'arrive toujours pas à trouver de réponse...

Dans ma macro, je souhaite, dans une feuille "sommaire", créer un lien vers une seconde feuille, et ensuite, effectuer une multi selection avec RANGE et select. Cependant, la combinaison de ces 2 actions provoque la fameuse Erreur d'exécution '1004': la méthode 'range' de l'objet '_global' a échoué.

Au niveau du code, cela donne une fois simplifié :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
    'Creation of a new book
    Workbooks.Add
    'Get the workbook name
    newbook = ActiveWorkbook.name
 
    'Create the summary sheet
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.name = "SUMMARY"
 
    'TITRE + FONT
    Range("E1").Select
    Selection.Value = "Summary"
 
    summary_row = 3   'first line of the summary
 
    'Creation of the customer sheet
     Sheets.Add After:=Sheets(Sheets.Count)
 
    'initialization of the customer sheet name
     namesheet = Workbooks(oldbook).Sheets("General Information").Cells(deb, colitem).Value
     ActiveSheet.name = namesheet
 
    'add a link in the summary sheet
    Workbooks(newbook).Sheets("SUMMARY").Cells(summary_row, 2).Value = Workbooks(oldbook).Sheets("General Information").Cells(deb, colname).Value
 
    'Partie qui, si supprimée, règle le problème.     
    Workbooks(newbook).Sheets("SUMMARY").Hyperlinks.Add Anchor:=Workbooks(newbook).Sheets("SUMMARY").Cells(summary_row, 2), Address:="", SubAddress:= _
    namesheet & "!A1", TextToDisplay:=Workbooks(oldbook).Sheets("General Information").Cells(deb, colname).Value
 
 
    'Selection qui va provoquer l'erreur
    Range("I1:K1,D3:I5,A8:E8,G8:K8,G9:K13,B15:D15,G15:K15,G16:K16,G17:K17,G18:K18,G19:K19,G20:K20,A17:E17,A19:E19,A22:K22").Select

Le problème, c'est que dans ma macro entière, j'ai besoin de ces deux "actions" et le tout est placé dans une boucle, donc pas moyen d'y échapper... et j'aimerais vraiment comprendre le lien entre une selection et un lien hypertexte.

Plus étonnant encore, lorsque je lançais cette macro de mon ordinateur de boulot avec EXCEL en version anglaise, cela marchait...

Merci beaucoup à ceux qui pourront me porter secours


Baptiste.

[fred65200]

bonsoir et bienvenue sur le forum

Sur quelle version d'excel travailles-tu?

fc

[baba77290]

Bonjour,

Merci pour ta réponse.

Effectivement, j'ai oublié de le préciser ^^
Je travaille sous Excel 2007, version française.

[JMVBAExcel]

En passant sur ce mel... L'erreur 1004 est souvent provoquée par des manipulations de cellules verrouillées sur des feuilles protégées... Le programme ne peut y accéder... malgré un code juste... Au cas où cela pourrait t'aider.

JM

[baba77290]

C'est gentil pour l'info mais ce n'est pas mon cas, il n'y a aucune histoire de cellule verrouillée et de feuille protégée ... ce code s'applique à des feuilles nouvellement créées.

Y a-t-il quelqu'un qui puisse m'aider ?

[Godzestla]

Bonjour,

peux-tu montrer ton code complet car ce fameux .select (à éviter comme la peste, et oui !!) doit quand même bien servir à quelquechose après non ?

Peux-tu expliquer le but et pas la manière ?

[baba77290]

Bonjour,

Excusez moi, je voulais éviter de mettre le code complet qui est un peu long, le voici : (la partie qui nous intéresse est plutot au début)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
Public Sub Macro2()
'
' Macro2 Macro
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''  MACRO TO CREATE ALL THE CUSTOMER CARDS  ''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' La Loggia Baptiste
' December 2008
 
 
'''''''''''''''''''''''''''''''  PARAMETERS  ''''''''''''''''''''''''''''''''''
 
'Initialization of the constants
ACtableHP = 27              'The row number of the AC table's head part
ACtableFL = ACtableHP + 1   'The row number of the AC table's first line
SUMMARYTABLEFL = 24         'The row number of the Summary table's first line
 
    'Initialization of the parameters
    oldbook = ActiveWorkbook.name
 
    'GENERAL
    colname = Sheets("Parameters").Cells(6, 4).Value
    colSN = Sheets("Parameters").Cells(7, 4).Value
    coltype = Sheets("Parameters").Cells(8, 4).Value
    colconfig = Sheets("Parameters").Cells(9, 4).Value
    colhours = Sheets("Parameters").Cells(10, 4).Value
    colupdated = Sheets("Parameters").Cells(11, 4).Value
    colsegment = Sheets("Parameters").Cells(12, 4).Value
    colkam = Sheets("Parameters").Cells(13, 4).Value
    colcontact = Sheets("Parameters").Cells(14, 4).Value
    colnumber = Sheets("Parameters").Cells(15, 4).Value
    colbeginning = Sheets("Parameters").Cells(16, 4).Value
    colexpiry = Sheets("Parameters").Cells(17, 4).Value
    colitem = Sheets("Parameters").Cells(18, 4).Value
    colcode = Sheets("Parameters").Cells(19, 4).Value
    colstatus = Sheets("Parameters").Cells(20, 4).Value
 
    'MAIN ADDRESS
    colentityA = Sheets("Parameters").Cells(61, 4).Value
    coladdrA = Sheets("Parameters").Cells(62, 4).Value
    colpostcodeA = Sheets("Parameters").Cells(63, 4).Value
    coltownA = Sheets("Parameters").Cells(64, 4).Value
    coldistrictA = Sheets("Parameters").Cells(65, 4).Value
    collocationA = Sheets("Parameters").Cells(66, 4).Value
    colcontactA = Sheets("Parameters").Cells(67, 4).Value
    coltitleA = Sheets("Parameters").Cells(68, 4).Value
    coltelA = Sheets("Parameters").Cells(69, 4).Value
    colfaxA = Sheets("Parameters").Cells(70, 4).Value
    colEmailA = Sheets("Parameters").Cells(71, 4).Value
 
 
    'EXPRESS ADDRESS (Tech pub)
    colentityE = Sheets("Parameters").Cells(22, 4).Value
    coladdrE = Sheets("Parameters").Cells(23, 4).Value
    colpostcodeE = Sheets("Parameters").Cells(24, 4).Value
    coltownE = Sheets("Parameters").Cells(25, 4).Value
    coldistrictE = Sheets("Parameters").Cells(26, 4).Value
    collocationE = Sheets("Parameters").Cells(27, 4).Value
    colcontactE = Sheets("Parameters").Cells(28, 4).Value
    coltitleE = Sheets("Parameters").Cells(29, 4).Value
    coltelE = Sheets("Parameters").Cells(30, 4).Value
    colfaxE = Sheets("Parameters").Cells(31, 4).Value
    colEmailE = Sheets("Parameters").Cells(32, 4).Value
 
    'INVOICING ADDRESS
    colentityI = Sheets("Parameters").Cells(35, 4).Value
    coladdrI = Sheets("Parameters").Cells(36, 4).Value
    colpostcodeI = Sheets("Parameters").Cells(37, 4).Value
    coltownI = Sheets("Parameters").Cells(38, 4).Value
    coldistrictI = Sheets("Parameters").Cells(39, 4).Value
    collocationI = Sheets("Parameters").Cells(40, 4).Value
    colcontactI = Sheets("Parameters").Cells(41, 4).Value
    coltitleI = Sheets("Parameters").Cells(42, 4).Value
    coltelI = Sheets("Parameters").Cells(43, 4).Value
    colfaxI = Sheets("Parameters").Cells(44, 4).Value
    colEmailI = Sheets("Parameters").Cells(45, 4).Value
 
    'SHIPPING ADDRESS
    colentityO = Sheets("Parameters").Cells(48, 4).Value
    coladdrO = Sheets("Parameters").Cells(49, 4).Value
    colpostcodeO = Sheets("Parameters").Cells(50, 4).Value
    coltownO = Sheets("Parameters").Cells(51, 4).Value
    coldistrictO = Sheets("Parameters").Cells(52, 4).Value
    collocationO = Sheets("Parameters").Cells(53, 4).Value
    colcontactO = Sheets("Parameters").Cells(54, 4).Value
    coltitleO = Sheets("Parameters").Cells(55, 4).Value
    coltelO = Sheets("Parameters").Cells(56, 4).Value
    colfaxO = Sheets("Parameters").Cells(57, 4).Value
    colEmailO = Sheets("Parameters").Cells(58, 4).Value
 
'
lig1 = Sheets("Parameters").Cells(6, 5).Value
'
    lig1tab = lig1 + 1
    deb = lig1tab
 
    'Creation of a new book
    Workbooks.Add
    'Get the workbook name
    newbook = ActiveWorkbook.name
 
    '''''''''''''''''''''''''''''           SUMMARY SHEET           ''''''''''''''''''''''''''''''''
    'Create the summary sheet
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.name = "SUMMARY"
 
    'TITRE + FONT
    Range("E1").Select
    Selection.Value = "Summary"
    Selection.HorizontalAlignment = xlCenter
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
        .Size = 20
        .Underline = xlUnderlineStyleSingle
        .Bold = True
    End With
 
    summary_row = 3   'first line of the summary
 
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''           MAIN LOOP           ''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'While the name cell is not empty
While (Workbooks(oldbook).Sheets("General Information").Cells(deb, colname) <> "")
 
 
     'Creation of the customer sheet
         Sheets.Add After:=Sheets(Sheets.Count)
     'stock the name of the CUSTOMER sheet
         If Workbooks(oldbook).Sheets("General Information").Cells(deb, colitem).Value <> "" Then
           'by the value of the 'item'
           namesheet = Workbooks(oldbook).Sheets("General Information").Cells(deb, colitem).Value
         Else 'or if the 'item' is empty, by 'NoName ...'
           namesheet = "NoName" & deb
         End If
 
    'initialization of the customer sheet name
     ActiveSheet.name = namesheet
 
     'add a link in the summary sheet
       Workbooks(newbook).Sheets("SUMMARY").Cells(summary_row, 2).Value = Workbooks(oldbook).Sheets("General Information").Cells(deb, colname).Value
       Workbooks(newbook).Sheets("SUMMARY").Hyperlinks.Add Anchor:=Workbooks(newbook).Sheets("SUMMARY").Cells(summary_row, 2), Address:="", SubAddress:= _
       namesheet & "!A1", TextToDisplay:=Workbooks(oldbook).Sheets("General Information").Cells(deb, colname).Value
 
       Workbooks(newbook).Sheets("SUMMARY").Select
       Range(Cells(summary_row, 2), Cells(summary_row, 7)).Select
       Selection.Merge
 
     'Delete the default sheets (only the first time)
     If deb = lig1tab Then
         'delete the other sheets
         Application.DisplayAlerts = False
         Sheets("Sheet3").Select
         ActiveWindow.SelectedSheets.Delete
         Sheets("Sheet2").Select
         ActiveWindow.SelectedSheets.Delete
         Sheets("Sheet1").Select
         ActiveWindow.SelectedSheets.Delete
     End If
 
 
    Workbooks(newbook).Sheets(namesheet).Activate
 
    'FORMAT PAGE
 
    Range("I1:K1").Merge
    Range("D3:I5").Merge
    Range("A8:E8").Merge
    Range("G8:K8").Merge
    Range("G9:K13").Merge
    Range("B15:D15").Merge
    Range("A17:E17").Merge
    Range("A19:E19").Merge
    Range("G15:K15").Merge
    Range("G16:K16").Merge
    Range("G17:K17").Merge
    Range("G18:K18").Merge
    Range("G19:K19").Merge
    Range("G20:K20").Merge
    Range("A22:K22").Merge
 
    Range("A9:E13").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = True
    End With
    Selection.Merge
 
' PARTIE QUI PROVOQUE L'ERREUR    Range("I1:K1,D3:I5,A8:E8,G8:K8,G9:K13,B15:D15,G15:K15,G16:K16,G17:K17,G18:K18,G19:K19,G20:K20,A17:E17,A19:E19,A22:K22").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
 
    Range("D3:I5,A8:E8,G8:K8,B15:D15,A17:E17,A19:E19,G15:K15,A22:K22,B" & ACtableHP & ":J" & ACtableHP).Select
    Range("A22").Activate
    Selection.Font.Bold = True
    Range("D3:I5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .name = "Calibri"
        .Size = 14
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("A8:E8,A19:E19,A17:E17").Select
    Range("A17").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
        .PatternTintAndShade = 0
    End With
    Range("G8:K8,G15:K15,A22:K22").Select
    Range("G15").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.349986266670736
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
 
    'Head part of the aircraft table
    Range("B" & ACtableHP & ":J" & ACtableHP).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
 
 
    '''''''''''''''''''''''''''''           PAGE LAYOUT           ''''''''''''''''''''''''''''''''
 
    Workbooks(newbook).Sheets(namesheet).Activate
 
    'Page Margins
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
    End With
 
    'Columns size
    Columns("A:A").ColumnWidth = 7
    Columns("E:E").ColumnWidth = 9
    Columns("F:F").ColumnWidth = 2.5
    Columns("I:I").ColumnWidth = 10.5
    Columns("J:J").ColumnWidth = 10.5
    Columns("G:G").ColumnWidth = 7
    Columns("K:K").ColumnWidth = 8
 
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''    Fill the sheet    '''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
 
    'link to go to the SUMMARY
    Workbooks(newbook).Sheets(namesheet).Cells(1, 5).Value = "summary"
    Workbooks(newbook).Sheets(namesheet).Hyperlinks.Add Anchor:=Workbooks(newbook).Sheets(namesheet). _
    Range(Cells(1, 5), Cells(1, 7)), Address:="", SubAddress:="SUMMARY!A1", TextToDisplay:="summary"
 
    Workbooks(newbook).Sheets(namesheet).Range(Cells(1, 5), Cells(1, 7)).Select
    Selection.Merge
    Selection.HorizontalAlignment = xlCenter
 
    'Copy of the EC logo
    Workbooks(oldbook).Activate
    Sheets("Parameters").Shapes(EClogo).Copy
    Workbooks(newbook).Activate
    Sheets(namesheet).Cells(1, 1).Select
    Sheets(namesheet).Paste
 
    'Name of the customer
    Workbooks(newbook).Sheets(namesheet).Cells(3, 4).Value = Workbooks(oldbook).Sheets("General Information").Cells(deb, colname).Value
 
    'Customer code
    Workbooks(newbook).Sheets(namesheet).Cells(1, 9).Value = Workbooks(oldbook).Sheets("Parameters").Cells(19, 2).Value & " : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, colcode).Value
 
    'Put the date
    Workbooks(newbook).Sheets(namesheet).Cells(5, 1).Value = "Issue on : " & Date
 
    'Market Segment
    Workbooks(newbook).Sheets(namesheet).Cells(8, 1).Value = Workbooks(oldbook).Sheets("Parameters").Cells(12, 2).Value & " : "
    Workbooks(newbook).Sheets(namesheet).Cells(9, 1).Value = Workbooks(oldbook).Sheets("General Information").Cells(deb, colsegment).Value
 
    'ENTITY
    If Workbooks(oldbook).Sheets("General Information").Cells(deb, collocationE).Value = "HONG KONG" Then
           Workbooks(newbook).Sheets(namesheet).Cells(15, 2).Value = "ENTITY : ECCN HONG KONG"
    Else
           If Workbooks(oldbook).Sheets("General Information").Cells(deb, collocationE).Value = "CHINA" Then
                  Workbooks(newbook).Sheets(namesheet).Cells(15, 2).Value = "ENTITY : ECCN SHANGHAI"
           End If
    End If
 
    'EC KAM
    Workbooks(newbook).Sheets(namesheet).Cells(17, 1).Value = "EC KAM : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, colkam).Value
 
    'ECCN CONTACT
    Workbooks(newbook).Sheets(namesheet).Cells(19, 1).Value = " ECCN CONTACT : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, colcontact).Value
 
    'Main contact + TITLE + TEL + FAX + EMAIL (A)
    Workbooks(newbook).Sheets(namesheet).Cells(15, 7).Value = "MAIN CONTACT"
    Workbooks(newbook).Sheets(namesheet).Cells(16, 7).Value = Workbooks(oldbook).Sheets("Parameters").Cells(67, 2).Value & " : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, colcontactA).Value
    Workbooks(newbook).Sheets(namesheet).Cells(17, 7).Value = Workbooks(oldbook).Sheets("Parameters").Cells(68, 2).Value & " : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, coltitleA).Value
    Workbooks(newbook).Sheets(namesheet).Cells(18, 7).Value = Workbooks(oldbook).Sheets("Parameters").Cells(69, 2).Value & " : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, coltelA).Value
    Workbooks(newbook).Sheets(namesheet).Cells(19, 7).Value = Workbooks(oldbook).Sheets("Parameters").Cells(70, 2).Value & " : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, colfaxA).Value
    Workbooks(newbook).Sheets(namesheet).Cells(20, 7).Value = Workbooks(oldbook).Sheets("Parameters").Cells(71, 2).Value & " : " & Workbooks(oldbook).Sheets("General Information").Cells(deb, colEmailA).Value
 
    'Main Address (A)
    Workbooks(newbook).Sheets(namesheet).Cells(8, 7).Value = "MAIN ADDRESS"
    If Workbooks(oldbook).Sheets("General Information").Cells(deb, coldistrictA).Value <> "" Then
        DistrictA = " (" & Workbooks(oldbook).Sheets("General Information").Cells(deb, coldistrictA).Value & ")"
    Else
        DistrictA = Workbooks(oldbook).Sheets("General Information").Cells(deb, coldistrictA).Value
    End If
    Workbooks(newbook).Sheets(namesheet).Cells(9, 7).Value = Workbooks(oldbook).Sheets("General Information").Cells(deb, colentityA).Value & Chr(10) & Workbooks(oldbook).Sheets("General Information").Cells(deb, coladdrA).Value & Chr(10) & Workbooks(oldbook).Sheets("General Information").Cells(deb, colpostcodeA).Value & " " & Workbooks(oldbook).Sheets("General Information").Cells(deb, coltownA).Value & DistrictA & Chr(10) & Workbooks(oldbook).Sheets("General Information").Cells(deb, collocationA).Value
 
 
    'Fleet part : title + head part
    Workbooks(newbook).Sheets(namesheet).Cells(22, 1).Value = "Technical Information concerning the customer fleet"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 2).Value = "A/C type"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 3).Value = "Config"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 4).Value = "S/N"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 5).Value = "Flight hours"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 6).Value = "Updated on"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 8).Value = "Contract Number"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 9).Value = "Date of beginning"
    Workbooks(newbook).Sheets(namesheet).Cells(ACtableHP, 10).Value = "Date of expiry"
 
 
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''     LOOP TO FILL ALL THE INFORMATION IN THE AIRCRAFT TABLE     '''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
........
 
 
  ''''''''''''''''''''''''''''''''''''         Aircraft table layout         ''''''''''''''''''''''''''''''''''''''
 
 ....
 
 
 ''''''''''''''''''''''''''''''''''''         Sorting the A/C data         ''''''''''''''''''''''''''''''''''''''
 
....
 
 
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 '''''''''''''''''''''''''''''         Go through the Aircraft table         ''''''''''''''''''''''''''''''''
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 ....
 
 
 
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 '''''''''''''''''''''''''''''         Write the different addresses         '''''''''''''''''''''''''''''''
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ........   
 
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 '''''''''''''''''''''''''''''''''         Write the status part         '''''''''''''''''''''''''''''''''''
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
    ....
 
'Just to make the reading more convenient
Workbooks(newbook).Sheets(namesheet).Cells(3, 4).Select
 
'New values for parameters
ACtableHP = 27              'The row number of the AC table's head part
ACtableFL = ACtableHP + 1   'The row number of the AC table's first line
 
'New value for "deb"
deb = fin + 1
 
'New value for "summary_row"
summary_row = summary_row + 1
 
 
Wend
 
 
End Sub

J'utilise le .select pour selectionner plusieurs cellules et mettre en forme une fiche. Cela m'évite de selectionner les cellules une à une pour les centrer, les mettre en gras... Et j'avoue que, débutant à VBA, je ne sais pas si il y a d'autres manières plus efficaces (et sans utiliser le .select) pour faire ça.

Je vous remercie encore pour votre aide.

Baptiste La Loggia