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

TraderAS Messages postés 110 Date d'inscription Statut Membre Dernière intervention -
TraderAS Messages postés 110 Date d'inscription Statut Membre Dernière intervention - 22 févr. 2013 à 17:48

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

Afficher la suite

A voir également:

Optimisation avec utilisation d'une boucle for i=1 do
Optimisation pc - Accueil - Utilitaires
Downloader for pc - Télécharger - Téléchargement & Transfert
Utilisation chromecast - Guide
Télécharger gratuitement notice d'utilisation - Guide
Optimisation découpe panneau gratuit - Télécharger - Outils professionnels

3 réponses

Réponse 1 / 3

pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention 2 761

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

Réponse 2 / 3

TraderAS Messages postés 110 Date d'inscription Statut Membre Dernière intervention 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

pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention 2 761

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...

Réponse 3 / 3

TraderAS Messages postés 110 Date d'inscription Statut Membre Dernière intervention 1

ca devient parfait
merci pijaku

Discussions similaires

i tréma

Bouton ON OFF, O ou I ?

les 2 petits points sur le e ou sur le i

symbole arrêt marche

securité i love pdf

Votre réponse

Discussions similaires