Option Explicit Private f As Worksheet, numtrait As Integer Private Sub Workbook_Open() regroupement End Sub Private Sub regroupement() Application.ScreenUpdating = False Dim nom_classeur As String, nom_feuille As String Set f = ThisWorkbook.Worksheets("Fichier de contrôle") f.Cells.ClearContents nom_classeur = "F:\PROJET DADS-U\URSAFF 1.XLS" nom_feuille = "Export 0" copions_a_la_suite nom_classeur, nom_feuille nom_classeur = "F:\PROJET DADS-U\URSAFF 1.XLS" nom_feuille = "Export 1" copions_a_la_suite nom_classeur, nom_feuille nom_classeur = "F:\PROJET DADS-U\URSAFF 1.XLS" nom_feuille = "Export 2" copions_a_la_suite nom_classeur, nom_feuille nom_classeur = "F:\PROJET DADS-U\URSAFF 1.XLS" nom_feuille = "Export 3" copions_a_la_suite nom_classeur, nom_feuille nom_classeur = "F:\PROJET DADS-U\URSSAF 2.XLS" nom_feuille = "Export 0" copions_a_la_suite nom_classeur, nom_feuille Application.ScreenUpdating = True End Sub Private Sub copions_a_la_suite(cl0 As String, f0 As String) Dim classeur As Workbook, dercol_orig As Long, derlig_orig As Long, dercol_desti, premcol_orig Set classeur = Workbooks.Open(cl0) With classeur dercol_orig = .Sheets(f0).Cells(1, Columns.Count).End(xlToLeft).Column derlig_orig = .Sheets(f0).Range("A" & Rows.Count).End(xlUp).Row dercol_desti = f.Cells(1, Columns.Count).End(xlToLeft).Column + 1 premcol_orig = 8 If numtrait = 0 Then premcol_orig = 3 dercol_orig = 11 dercol_desti = 1 End If .Sheets(f0).Range(.Sheets(f0).Cells(1, premcol_orig), .Sheets(f0).Cells(derlig_orig, dercol_orig)).Copy Destination:=f.Cells(1, dercol_desti) numtrait = numtrait + 1 End With classeur.Close End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionOui pour la partie allant jusqu'à DRPRE (Prénom). Comme tu l'as vu dans mon fichier, les autres changent
Option Explicit Private f As Worksheet, numtrait As Integer Private Sub Workbook_Open() regroupement End Sub Private Sub regroupement() Application.ScreenUpdating = False Dim nom_classeur As String, nom_feuille As String Set f = ThisWorkbook.Worksheets("Fichier de contrôle") f.Cells.ClearContents nom_classeur = "D:\EXPORT0\URSAFF 1.XLS" copions_a_la_suite nom_classeur ', nom_feuille Application.ScreenUpdating = True End Sub Private Sub copions_a_la_suite(cl0 As String) Dim f0 As String Dim classeur As Workbook, dercol_orig As Long, derlig_orig As Long, dercol_desti, premcol_orig Set classeur = Workbooks.Open(cl0) Dim i As Integer For i = 0 To classeur.Worksheets.Count On Error Resume Next If classeur.Worksheets("Export " & i).Name = "Export " & i Then f0 = "Export " & i With classeur dercol_orig = .Sheets(f0).Cells(1, Columns.Count).End(xlToLeft).Column derlig_orig = .Sheets(f0).Range("A" & Rows.Count).End(xlUp).Row dercol_desti = f.Cells(1, Columns.Count).End(xlToLeft).Column + 1 premcol_orig = 8 If numtrait = 0 Then premcol_orig = 3 dercol_orig = 11 dercol_desti = 1 End If .Sheets(f0).Range(.Sheets(f0).Cells(1, premcol_orig), .Sheets(f0).Cells(derlig_orig, dercol_orig)).Copy Destination:=f.Cells(1, dercol_desti) numtrait = numtrait + 1 End With End If On Error GoTo 0 Next classeur.Close End Sub
quels changements faudrait il apporter
Sub Macro1() ' ' Macro1 Macro ' ' ActiveCell.FormulaR1C1 = _ "=VLOOKUP('Export 0'!RC[-9],'Export 1'!RC[-9]:R[39]C[-3],6,FALSE)" Range("L2").Select Selection.AutoFill Destination:=Range("L2:L41") Range("L2:L41").Select Range("L2").Select Selection.AutoFill Destination:=Range("L2:M2"), Type:=xlFillDefault Range("L2:M2").Select Range("M2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP('Export 0'!RC[-10],'Export 1'!RC[-10]:R[39]C[-4],7,FALSE)" Range("M2").Select Selection.AutoFill Destination:=Range("M2:M41") Range("M2:M41").Select End Sub
Sub recherche() ' ' recherche Macro ' Range("L2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'Export 1'!R2C3:R41C9,6,FALSE)" Selection.AutoFill Destination:=Range("L2:M2"), Type:=xlFillDefault Range("L2:M2").Select Range("M2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],'Export 1'!R2C3:R41C9,7,FALSE)" Range("M2").Select ActiveWindow.SmallScroll ToRight:=13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,'Export 1'!R2C3:R41C9,7,FALSE)" Range("M2").Select Selection.AutoFill Destination:=Range("M2:Q2"), Type:=xlFillDefault Range("M2:Q2").Select Range("N2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,'Export 2'!R2C3:R41C9,6,FALSE)" Range("O2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,'Export 2'!R2C3:R41C9,7,FALSE)" Range("P2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,'Export 3'!R2C3:R41C9,6,FALSE)" Range("Q2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,'Export 3'!R2C3:R41C9,7,FALSE)" Range("L2:Q2").Select Range("Q2").Activate ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 Selection.AutoFill Destination:=Range("L2:Q41") Range("L2:Q41").Select ActiveWindow.LargeScroll ToRight:=-1 Range("A2").Select End Sub