Optimisation avec utilisation d'une boucle for i=1 do

TraderAS Messages postés 119 Statut Membre -  
TraderAS Messages postés 119 Statut Membre -
Bonjour,

je suis sur VBA/Excel

je souhaite optimiser un code que j'ai écri, il est trop long, en faite cette procédure se répète pour chaque fichier excel que j'ouvre,
ma question est comment écrire une boucle genre for i=1 à 50 do

Workbooks.Open Rep & FichHexaByte
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B64:G64").Copy
Workbooks(FichHexaByte).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichHexaByte).Save
Workbooks(FichHexaByte).Close
End With

le code principal est:

Sub CopierColler()
Application.ScreenUpdating = False
Rep = "C:\Users\toshiba\Desktop\TRADING\Historical\"
FichD = ActiveWorkbook.Name
FichAdwya = "Adwya.xlsm"
FichAirLikid = "AirLikid.xlsm"
FichAlkimia = "Alkimia.xlsm"
FichAmenBank = "AmenBank.xlsm"
FichAMS = "AMS.xlsm"
FichArtes = "Artes.xlsm"
FichAssad = "Assad.xlsm"
FichAstree = "Astree.xlsm"
FichATB = "ATB.xlsm"
FichATL = "ATL.xlsm"
FichAttijariBankAttijariBank = "AttijariBank.xlsm"
FichAttijariLeasing = "AttijariLeasing.xlsm"
FichBH = "BH.xlsm"
FichBIAT = "BIAT.xlsm"
FichBNA = "BNA.xlsm"
FichBT = "BT.xlsm"
FichBTE = "BTE.xlsm"
FichCC = "CC.xlsm"
FichCIL = "CIL.xlsm"
FichCimentsdeBizerte = "Ciments de Bizerte.xlsm"
FichElecrostar = "Elecrostar.xlsm"
FichEnnakl = "Ennakl.xlsm"
FichESSOKNA = "ESSOKNA.xlsm"
FichGIF = "GIF.xlsm"
FichHexaByte = "HexaByte.xlsm"
FichICF = "ICF.xlsm"
FichMagasinGeneral = "MagasinGeneral.xlsm"
FichModernLeasing = "ModernLeasing.xlsm"
FichMonoprix = "Monoprix.xlsm"
FichPLTU = "PLTU.xlsm"
FichPoulina = "Poulina.xlsm"
FichSalimAssurances = "Salim Assurances.xlsm"
FichServicom = "Servicom.xlsm"
FichSFBT = "SFBT.xlsm"
FichSIAME = "SIAME.xlsm"
FichSIMPAR = "SIMPAR.xlsm"
FichSIPHAT = "SIPHAT.xlsm"
FichSITEX = "SITEX.xlsm"
FichSociétéLanDor = "SociétéLanDor.xlsm"
FichSomocer = "Somocer.xlsm"
FichSOPAT = "Sopat.xlsm"
FichSOTETEL = "Sotetel.xlsm"
FichSotrapil = "Sotrapil.xlsm"
FichSotumag = "Sotumag.xlsm"
FichSOTUVER = "Sotuver.xlsm"
FichSOTUVER = "Sotuver.xlsm"
FichSPDIT = "SPDIT.xlsm"
FichSTAR = "STAR.xlsm"
FichSTB = "STB.xlsm"
FichSTEQ = "STEQ.xlsm"
FichSTIP = "STIP.xlsm"
FichSTS = "STS.xlsm"
FichTelNet = "TelNet.xlsm"
FichTPR = "TPR.xlsm"
FichTunInvest = "TunInvest.xlsm"
FichTunisair = "Tunisair.xlsm"
FichTunisieLait = "TunisieLait.xlsm"
FichTunisieLeasing = "TunisieLeasing.xlsm"
FichTunisRe = "TunisRe.xlsm"
FichUBCI = "UBCI.xlsm"
FichUIB = "UIB.xlsm"
FichAlWifackLeasing = "AlWifackLeasing.xlsm"
FichSITS = "SITS.xlsm"

Workbooks.Open Rep & FichAdwya
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B6:G6").Copy
Workbooks(FichAdwya).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAdwya).Save
Workbooks(FichAdwya).Close
End With

Workbooks.Open Rep & FichAirLikid
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B7:G7").Copy
Workbooks(FichAirLikid).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAirLikid).Save
Workbooks(FichAirLikid).Close
End With

Workbooks.Open Rep & FichAlkimia
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B8:G8").Copy
Workbooks(FichAlkimia).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAlkimia).Save
Workbooks(FichAlkimia).Close
End With

Workbooks.Open Rep & FichAmenBank
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B5:G5").Copy
Workbooks(FichAmenBank).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAmenBank).Save
Workbooks(FichAmenBank).Close
End With

Workbooks.Open Rep & FichAMS
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B9:G9").Copy
Workbooks(FichAMS).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAMS).Save
Workbooks(FichAMS).Close
End With

Workbooks.Open Rep & FichArtes
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B10:G10").Copy
Workbooks(FichArtes).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichArtes).Save
Workbooks(FichArtes).Close
End With

Workbooks.Open Rep & FichAssad
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B11:G11").Copy
Workbooks(FichAssad).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAssad).Save
Workbooks(FichAssad).Close
End With

Workbooks.Open Rep & FichAstree
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B12:G12").Copy
Workbooks(FichAstree).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAstree).Save
Workbooks(FichAstree).Close
End With

Workbooks.Open Rep & FichATB
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B13:G13").Copy
Workbooks(FichATB).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichATB).Save
Workbooks(FichATB).Close
End With

Workbooks.Open Rep & FichATL
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B14:G14").Copy
Workbooks(FichATL).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichATL).Save
Workbooks(FichATL).Close
End With

Workbooks.Open Rep & FichBH
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B15:G15").Copy
Workbooks(FichBH).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichBH).Save
Workbooks(FichBH).Close
End With

Workbooks.Open Rep & FichBIAT
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B16:G16").Copy
Workbooks(FichBIAT).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichBIAT).Save
Workbooks(FichBIAT).Close
End With

Workbooks.Open Rep & FichBNA
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B17:G17").Copy
Workbooks(FichBNA).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichBNA).Save
Workbooks(FichBNA).Close
End With

Workbooks.Open Rep & FichBT
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B18:G18").Copy
Workbooks(FichBT).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichBT).Save
Workbooks(FichBT).Close
End With

Workbooks.Open Rep & FichBTE
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B19:G19").Copy
Workbooks(FichBTE).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichBTE).Save
Workbooks(FichBTE).Close
End With

Workbooks.Open Rep & FichCC
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B20:G20").Copy
Workbooks(FichCC).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichCC).Save
Workbooks(FichCC).Close
End With

Workbooks.Open Rep & FichCIL
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B21:G21").Copy
Workbooks(FichCIL).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichCIL).Save
Workbooks(FichCIL).Close
End With

Workbooks.Open Rep & FichGIF
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B22:G22").Copy
Workbooks(FichGIF).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichGIF).Save
Workbooks(FichGIF).Close
End With

Workbooks.Open Rep & FichICF
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B23:G23").Copy
Workbooks(FichICF).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichICF).Save
Workbooks(FichICF).Close
End With

Workbooks.Open Rep & FichElecrostar
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B24:G24").Copy
Workbooks(FichElecrostar).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichElecrostar).Save
Workbooks(FichElecrostar).Close
End With

Workbooks.Open Rep & FichMagasinGeneral
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B25:G25").Copy
Workbooks(FichMagasinGeneral).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichMagasinGeneral).Save
Workbooks(FichMagasinGeneral).Close
End With

Workbooks.Open Rep & FichModernLeasing
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B27:G27").Copy
Workbooks(FichModernLeasing).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichModernLeasing).Save
Workbooks(FichModernLeasing).Close
End With

Workbooks.Open Rep & FichMonoprix
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B28:G28").Copy
Workbooks(FichMonoprix).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichMonoprix).Save
Workbooks(FichMonoprix).Close
End With

Workbooks.Open Rep & FichEnnakl
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B29:G29").Copy
Workbooks(FichEnnakl).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichEnnakl).Save
Workbooks(FichEnnakl).Close
End With

Workbooks.Open Rep & FichPoulina
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B30:G30").Copy
Workbooks(FichPoulina).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichPoulina).Save
Workbooks(FichPoulina).Close
End With

Workbooks.Open Rep & FichPLTU
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B31:G31").Copy
Workbooks(FichPLTU).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichPLTU).Save
Workbooks(FichPLTU).Close
End With

Workbooks.Open Rep & FichSalimAssurances
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B32:G32").Copy
Workbooks(FichSalimAssurances).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSalimAssurances).Save
Workbooks(FichSalimAssurances).Close
End With

Workbooks.Open Rep & FichCimentsdeBizerte
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B33:G33").Copy
Workbooks(FichCimentsdeBizerte).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichCimentsdeBizerte).Save
Workbooks(FichCimentsdeBizerte).Close
End With

Workbooks.Open Rep & FichServicom
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B34:G34").Copy
Workbooks(FichServicom).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichServicom).Save
Workbooks(FichServicom).Close
End With

Workbooks.Open Rep & FichSFBT
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B35:G35").Copy
Workbooks(FichSFBT).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSFBT).Save
Workbooks(FichSFBT).Close
End With

Workbooks.Open Rep & FichSIAME
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B36:G36").Copy
Workbooks(FichSIAME).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSIAME).Save
Workbooks(FichSIAME).Close
End With

Workbooks.Open Rep & FichSIMPAR
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B37:G37").Copy
Workbooks(FichSIMPAR).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSIMPAR).Save
Workbooks(FichSIMPAR).Close
End With

Workbooks.Open Rep & FichSIPHAT
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B38:G38").Copy
Workbooks(FichSIPHAT).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSIPHAT).Save
Workbooks(FichSIPHAT).Close
End With

Workbooks.Open Rep & FichSITEX
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B39:G39").Copy
Workbooks(FichSITEX).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSITEX).Save
Workbooks(FichSITEX).Close
End With

Workbooks.Open Rep & FichSITS
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B40:G40").Copy
Workbooks(FichSITS).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSITS).Save
Workbooks(FichSITS).Close
End With

Workbooks.Open Rep & FichESSOKNA
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B41:G41").Copy
Workbooks(FichESSOKNA).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichESSOKNA).Save
Workbooks(FichESSOKNA).Close
End With

Workbooks.Open Rep & FichSomocer
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B42:G42").Copy
Workbooks(FichSomocer).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSomocer).Save
Workbooks(FichSomocer).Close
End With

Workbooks.Open Rep & FichSOPAT
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B43:G43").Copy
Workbooks(FichSOPAT).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSOPAT).Save
Workbooks(FichSOPAT).Close
End With

Workbooks.Open Rep & FichSOTETEL
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B44:G44").Copy
Workbooks(FichSOTETEL).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSOTETEL).Save
Workbooks(FichSOTETEL).Close
End With

Workbooks.Open Rep & FichSOTUVER
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B45:G45").Copy
Workbooks(FichSOTUVER).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSOTUVER).Save
Workbooks(FichSOTUVER).Close
End With

Workbooks.Open Rep & FichSPDIT
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B46:G46").Copy
Workbooks(FichSPDIT).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSPDIT).Save
Workbooks(FichSPDIT).Close
End With

Workbooks.Open Rep & FichSTAR
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B47:G47").Copy
Workbooks(FichSTAR).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSTAR).Save
Workbooks(FichSTAR).Close
End With

Workbooks.Open Rep & FichSTB
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B48:G48").Copy
Workbooks(FichSTB).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSTB).Save
Workbooks(FichSTB).Close
End With

Workbooks.Open Rep & FichSTEQ
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B49:G49").Copy
Workbooks(FichSTEQ).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSTEQ).Save
Workbooks(FichSTEQ).Close
End With

Workbooks.Open Rep & FichSotrapil
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B50:G50").Copy
Workbooks(FichSotrapil).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSotrapil).Save
Workbooks(FichSotrapil).Close
End With

Workbooks.Open Rep & FichSTS
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B51:G51").Copy
Workbooks(FichSTS).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichSTS).Save
Workbooks(FichSTS).Close
End With

Workbooks.Open Rep & FichTunisair
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B52:G52").Copy
Workbooks(FichTunisair).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTunisair).Save
Workbooks(FichTunisair).Close
End With

Workbooks.Open Rep & FichTunInvest
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B53:G53").Copy
Workbooks(FichTunInvest).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTunInvest).Save
Workbooks(FichTunInvest).Close
End With

Workbooks.Open Rep & FichAttijariBankAttijariBank
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B54:G54").Copy
Workbooks(FichAttijariBankAttijariBank).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAttijariBankAttijariBank).Save
Workbooks(FichAttijariBankAttijariBank).Close
End With

Workbooks.Open Rep & FichAttijariBankAttijariBank
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B54:G54").Copy
Workbooks(FichAttijariBankAttijariBank).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAttijariBankAttijariBank).Save
Workbooks(FichAttijariBankAttijariBank).Close
End With

Workbooks.Open Rep & FichAttijariLeasing
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B55:G55").Copy
Workbooks(FichAttijariLeasing).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAttijariLeasing).Save
Workbooks(FichAttijariLeasing).Close
End With

Workbooks.Open Rep & FichTunisieLait
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B56:G56").Copy
Workbooks(FichTunisieLait).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTunisieLait).Save
Workbooks(FichTunisieLait).Close
End With

Workbooks.Open Rep & FichTelNet
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B57:G57").Copy
Workbooks(FichTelNet).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTelNet).Save
Workbooks(FichTelNet).Close
End With

Workbooks.Open Rep & FichTunisieLeasing
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B58:G58").Copy
Workbooks(FichTunisieLeasing).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTunisieLeasing).Save
Workbooks(FichTunisieLeasing).Close
End With

Workbooks.Open Rep & FichTPR
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B59:G59").Copy
Workbooks(FichTPR).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTPR).Save
Workbooks(FichTPR).Close
End With

Workbooks.Open Rep & FichTunisRe
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B60:G60").Copy
Workbooks(FichTunisRe).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichTunisRe).Save
Workbooks(FichTunisRe).Close
End With

Workbooks.Open Rep & FichUBCI
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B61:G61").Copy
Workbooks(FichUBCI).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichUBCI).Save
Workbooks(FichUBCI).Close
End With

Workbooks.Open Rep & FichUIB
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B62:G62").Copy
Workbooks(FichUIB).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichUIB).Save
Workbooks(FichUIB).Close
End With

Workbooks.Open Rep & FichAlWifackLeasing
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B63:G63").Copy
Workbooks(FichAlWifackLeasing).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichAlWifackLeasing).Save
Workbooks(FichAlWifackLeasing).Close
End With

Workbooks.Open Rep & FichHexaByte
Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[A5] = [A6] + 1
With Workbooks(FichD)
.Sheets("Fundamentals").Range("B64:G64").Copy
Workbooks(FichHexaByte).Sheets("Feuil1").Range("B5:G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks(FichHexaByte).Save
Workbooks(FichHexaByte).Close
End With

End Sub

3 réponses

  1.
    pijaku Messages postés 13513 Date d'inscription   Statut Modérateur Dernière intervention   2 773
     
    Bonjour,

    Perso je ferais ceci :
    Mettre tous les fichiers concernés et uniquement ceux là dans un répertoire puis boucler sur tous les fichiers de ce répertoire.
    Ca donne à peu près ça : 

    Sub CopierColler()
Dim FichD As Workbook
Dim Rep As String
Dim Fichier As String

Set FichD = ActiveWorkbook
Application.ScreenUpdating = False
Rep = "C:\Users\toshiba\Desktop\TRADING\Historical\"
Fichier = Dir(Rep & "*.xls")
Do While Len(Fichier) > 0
    Workbooks.Open Rep & Fichier
    Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    [A5] = [A6] + 1
    With FichD
        .Sheets("Feuil1").Range("B64:G64").Copy
    End With
    With Workbooks(Fichier)
        .Sheets("Feuil1").Range("B5:G5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
        .Save
        .Close
    End With
Fichier = Dir()
Loop
Application.ScreenUpdating = True
End Sub


    Mais bon, au vu du nombre de fichiers à ouvrir, dans ton cas, vaudrait mieux passer par un ADO ou une autre méthode, sans ouvrir les fichiers....
    Voir cette discussion
    0
  2. TraderAS Messages postés 119 Statut Membre 1
     
    salut pijaku,

    un autre problème se manifeste, car chaque fichier ouvert lui correspond une ligne spécifique dans le Workbooks(FichD), or dans ton cas ta figé la ligne comme:

    With FichD
    .Sheets("Feuil1").Range("B64:G64").Copy
    End With

    TraderAS
    0
    1. pijaku Messages postés 13513 Date d'inscription   Statut Modérateur Dernière intervention   2 773
       
      Si le numéro de ligne n'a pas d'importance : 
      Sub CopierColler()
Dim FichD As Workbook
Dim Rep As String
Dim Fichier As String
Dim Ligne As Integer

Set FichD = ActiveWorkbook
Application.ScreenUpdating = False
Ligne = 5
Rep = "C:\Users\toshiba\Desktop\TRADING\Historical\"
Fichier = Dir(Rep & "*.xls")
Do While Len(Fichier) > 0
    Workbooks.Open Rep & Fichier
    Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    [A5] = [A6] + 1
    With FichD
        .Sheets("Feuil1").Range("B64:G64").Copy
    End With
    With Workbooks(Fichier)
        .Sheets("Feuil1").Range("B" & Ligne & ":G" & Ligne).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
        .Save
        .Close
    End With
Fichier = Dir()
Ligne = Ligne + 1
Loop
Application.ScreenUpdating = True
End Sub

      Sinon, tu te créées une variable tableau à deux dimensions avec en 1ere dimension les noms des classeurs et en 2nde la ligne correspondante...

      De retour lundi...
      0
  3. TraderAS Messages postés 119 Statut Membre 1
     
    ca devient parfait
    merci pijaku
    0

Discussions similaires

les 2 petits points sur le e ou sur le i
mimjca -
mimjca -

2 réponses
Bouton ON/OFF : O ou I, quel côté est allumé
Stargirlfr -
kaparzo23 -

12 réponses
''e'' et ''i'' Tréma
Rick -
lola -

7 réponses
Avis sur ilovepdf
klimte -
klimte -

4 réponses
Comment faire un i tréma
Johanna -
PSud -

11 réponses