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
| Dim dest As Worksheet
Dim source As Worksheet
Dim dernlig As Long
Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
Set source = ThisWorkbook.Sheets("Données")
dernlig = source.Range("C" & Rows.Count).End(xlUp).Row
dest.Cells.ClearContents
dest.Cells.Borders(xlDiagonalDown).LineStyle = xlNone
dest.Cells.Borders(xlDiagonalUp).LineStyle = xlNone
dest.Cells.Borders(xlEdgeLeft).LineStyle = xlNone
dest.Cells.Borders(xlEdgeTop).LineStyle = xlNone
dest.Cells.Borders(xlEdgeBottom).LineStyle = xlNone
dest.Cells.Borders(xlEdgeRight).LineStyle = xlNone
dest.Cells.Borders(xlInsideVertical).LineStyle = xlNone
dest.Cells.Borders(xlInsideHorizontal).LineStyle = xlNone
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$C$5:$C$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$1:$I$18"), False, False, False, False, , False
dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$D$5:$D$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$20:$I$38"), False, False, False, False, , False
dest.Range("B20").FormulaR1C1 = "=Données!R[-16]C[2]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$E$5:$E$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$40:$I$58"), False, False, False, False, , False
dest.Range("B40").FormulaR1C1 = "=Données!R[-36]C[3]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$F$5:$F$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$60:$I$78"), False, False, False, False, , False
dest.Range("B60").FormulaR1C1 = "=Données!R[-56]C[4]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$G$5:$G$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$80:$I$98"), False, False, False, False, , False
dest.Range("B80").FormulaR1C1 = "=Données!R[-76]C[5]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$H$5:$H$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$100:$I$118"), False, False, False, False, , False
dest.Range("B100").FormulaR1C1 = "=Données!R[-96]C[6]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$I$5:$I$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$120:$I$138"), False, False, False, False, , False
dest.Range("B120").FormulaR1C1 = "=Données!R[-116]C[7]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$J$5:$J$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$140:$I$158"), False, False, False, False, , False
dest.Range("B140").Select
ActiveCell.FormulaR1C1 = "=Données!R[-136]C[8]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$K$5:$K$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$160:$I$178"), False, False, False, False, , False
dest.Range("B160").FormulaR1C1 = "=Données!R[-156]C[9]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$L$5:$L$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$180:$I$198"), False, False, False, False, , False
dest.Range("B180").FormulaR1C1 = "=Données!R[-176]C[10]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$M$5:$M$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$200:$I$218"), False, False, False, False, , False
dest.Range("B200").FormulaR1C1 = "=Données!R[-196]C[11]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$N$65:$N$" & dernlig), _
source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$220:$I$238"), False, False, False, False, , False
dest.Range("B220").FormulaR1C1 = "=Données!R[-216]C[12]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$O$65:$O$" & dernlig), _
source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$240:$I$258"), False, False, False, False, , False
dest.Range("B240").FormulaR1C1 = "=Données!R[-236]C[13]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$P$173:$P$" & dernlig), _
source.Range("$B$173:$B$" & dernlig), False, False, , dest.Range("$A$260:$I$278"), False, False, False, False, , False
dest.Range("B260").FormulaR1C1 = "=Données!R[-256]C[14]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$Q$65:$Q$" & dernlig), _
source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$280:$I$298"), False, False, False, False, , False
dest.Range("B280").FormulaR1C1 = "=Données!R[-276]C[15]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$R$5:$R$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$300:$I$318"), False, False, False, False, , False
dest.Range("B300").FormulaR1C1 = "=Données!R[-296]C[16]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$S$5:$S$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$320:$I$338"), False, False, False, False, , False
dest.Range("B320").FormulaR1C1 = "=Données!R[-316]C[17]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$T$65:$T$" & dernlig), _
source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$340:$I$358"), False, False, False, False, , False
dest.Range("B340").FormulaR1C1 = "=Données!R[-336]C[18]"
Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$U$5:$U$" & dernlig), _
source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$360:$I$378"), False, False, False, False, , False
dest.Range("B360").FormulaR1C1 = "=Données!R[-356]C[19]"
End Sub |
Partager