Excel VBA – Iterate à travers des colonnes dans un classur, collant des informations dans le classur correspondant

J'ai des données actuelles dans un classur et des données archivées dans un autre classur. Dans la colonne "B" du classur de données récentes, j'ai une variable ID. Je veux dire:

Pour chacun des ID figurant dans la colonne B des données récentes, interrogez toutes les lignes de la colonne A du classur archivé. S'il y a une correspondance, copyz plusieurs inputs de colonne du classur de données récentes dans le classur archivé.

J'ai écrit un code de travail, mais le problème est que, dans le cahier des données archivées, il y a 1.048.575 lignes et les loops For fonctionnent très lentement pour chaque match. Y a-t-il une meilleure façon de penser à cela?

Voici mon code actuel:

• Comment insert une image dans Excel depuis l'application C #?
• Télécharger avec VBA via sFTP et FTP, save les sorties pour détecter les erreurs
• Comment un complément Excel peut-il répondre aux events dans une feuille de calcul?
• Excel Select vs Activer
• Insérez dynamicment la macro dans un nouveau classur Excel

Sub CopyDataLines() Dim wb As Workbook, wb2 As Workbook Dim ws As Worksheet Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Ssortingng 'Set source workbook Set wb = ActiveWorkbook Set wbSheet = ActiveSheet 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Workbooks.Open vFile 'Set worbook to copy from Set wb2 = ActiveWorkbook Set wb2sheet = ActiveSheet With wb2.ActiveSheet FirstRow_book2 = 3 LastRow_book2 = .Cells(.Rows.Count, "B").End(xlUp).Row 'The contents of the tracking book FirstRow_book1 = 3 LastRow_book1 = wbSheet.Cells(.Rows.Count, "A").End(xlUp).Row For Lrow = LastRow_book2 To FirstRow_book2 Step -1 With .Cells(Lrow, "B") Pupid = .Value End With 'The For Loop Now Iterates Through All of the First WorkBook For Lrow_book1 = LastRow_book1 To FirstRow_book1 Step -1 With wbSheet.Cells(Lrow_book1, "A") If .Value = Pupid Then 'Reference for Date Changed Cells wbSheet.Cells(Lrow_book1, "V") = wb2sheet.Cells(Lrow, "C") 'Reference for Date Changed Cells wbSheet.Cells(Lrow_book1, "X") = wb2sheet.Cells(Lrow, "D") 'Prepare to copy range of multiple columns Let secondBookRange = "I" & Lrow & ":" & "N" & Lrow Let firstBookRange = "AI" & Lrow_book1 & ":" & "AN" & Lrow_book1 wb2sheet.Range(secondBookRange).Copy Destination:=wbSheet.Range(firstBookRange) End If End With Next Lrow_book1 Next Lrow End With 

Mise en œuvre actuelle à l'aide d'un dictionary / carte Hash:

 Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Sub CopyLinesImproves () Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Dim vFile As Variant Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Dim Filter As Ssortingng Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Dim FilterIndex As Integer Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Dim Pupid As Long Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'Set Tracking Book Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définir wb_TrackingBook = ActiveWorkbook Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définissez wbSheet_TrackingBook = ActiveSheet Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'Définir la dernière ligne de TrackingBook Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function LastRow_TrackingBook = wbSheet_TrackingBook.Cells (wbSheet_TrackingBook.Rows.Count, "A"). End (xlUp) .Row Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'Filtres pour les files autorisés Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Filter = "Excel versions ultérieures (* .xlsx), *. Xlsx," & _ Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function "Fichiers Excel (* .xls), *. Xls," Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function FilterIndex = 1 Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'Ouvrir le cahier cible Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function vFile = Application.GetOpenFilename (Filter, FilterIndex, "Select One File to Open",, False) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'si l'user n'a pas sélectionné un file, quittez le sous Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Si TypeName (vFile) = "Boolean" Then Exit Sub Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'Ouvrez encore le file Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définissez wb_NewData = Workbooks.Open (vFile) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définir wbSheet_NewData = wb_NewData.ActiveSheet Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'Définir la première ligne et la dernière ligne de la nouvelle feuille de travail de données Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function FirstRow_NewData = 3 Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function LastRow_NewData = wbSheet_NewData.Cells (wbSheet_NewData.Rows.Count, "B"). End (xlUp) .Row Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 'créer une carte de consultation à l'aide d'un dictionary Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définissez rngLookup = wbSheet_TrackingBook.Range ("A1"). Redimensionner (LastRow_TrackingBook, 1) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définir d = GetMap (rngLookup) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Pour CurrentRow = FirstRow_NewData à LastRow_NewData Étape 1 Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Pupid = wbSheet_NewData.Cells (CurrentRow, "B"). Valeur Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Si d.exists (Pupid) Ensuite Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function wbSheet_TrackingBook.Cells (d (Pupid), "V") = wbSheet_NewData.Cells (CurrentRow, "C") Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function wbSheet_TrackingBook.Cells (d (Pupid), "X") = wbSheet_NewData.Cells (CurrentRow, "D") Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Laissez secondBookRange = "I" et CurrentRow & ":" & "N" et CurrentRow Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Laissez firstBookRange = "AI" et d (Pupid) & ":" & "AN" & d (Pupid) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function wbSheet_NewData.Range (secondBookRange) .Copy Destination: = wbSheet_TrackingBook.Range (firstBookRange) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Suivant CurrentRow Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Fonction GetMap (rng) As Object Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Dim d, v, arr, ub As Long, r As Long, r1 As Long Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Dim c As Range Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définir d = CreateObject ("scripting.dictionary") Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function arr = rng.Value Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function r1 = rng.Cells (1) .Row Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function ub = UBound (arr, 1) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Pour r = 1 à ub Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function v = arr (r, 1) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Si Len (v)> 0 Then Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Si d.exists (v) Ensuite Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function d (v) = d (v) & "|" Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function & r1 + (r - 1) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function d.Add v, r1 + (r-1) Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Next r Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function Définir GetMap = d Sub CopyLinesImproves() Dim vFile As Variant Dim Filter As Ssortingng Dim FilterIndex As Integer Dim Pupid As Long 'Set Tracking Book Set wb_TrackingBook = ActiveWorkbook Set wbSheet_TrackingBook = ActiveSheet 'Set Last Row of TrackingBook LastRow_TrackingBook = wbSheet_TrackingBook.Cells(wbSheet_TrackingBook.Rows.Count, "A").End(xlUp).Row 'Filters for allowed files Filter = "Excel Later Versions (*.xlsx),*.xlsx," & _ "Excel Files (*.xls),*.xls," FilterIndex = 1 'Open the target workbook vFile = Application.GetOpenFilename(Filter, FilterIndex, "Select One File to Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub 'Else open the file Set wb_NewData = Workbooks.Open(vFile) Set wbSheet_NewData = wb_NewData.ActiveSheet 'Set First Row and Last Row of the New Data Worksheet FirstRow_NewData = 3 LastRow_NewData = wbSheet_NewData.Cells(wbSheet_NewData.Rows.Count, "B").End(xlUp).Row 'create a lookup map using a dictionary Set rngLookup = wbSheet_TrackingBook.Range("A1").Resize(LastRow_TrackingBook, 1) Set d = GetMap(rngLookup) For CurrentRow = FirstRow_NewData To LastRow_NewData Step 1 Pupid = wbSheet_NewData.Cells(CurrentRow, "B").Value If d.exists(Pupid) Then wbSheet_TrackingBook.Cells(d(Pupid), "V") = wbSheet_NewData.Cells(CurrentRow, "C") wbSheet_TrackingBook.Cells(d(Pupid), "X") = wbSheet_NewData.Cells(CurrentRow, "D") Let secondBookRange = "I" & CurrentRow & ":" & "N" & CurrentRow Let firstBookRange = "AI" & d(Pupid) & ":" & "AN" & d(Pupid) wbSheet_NewData.Range(secondBookRange).Copy Destination:=wbSheet_TrackingBook.Range(firstBookRange) End If Next CurrentRow End Sub Function GetMap(rng) As Object Dim d, v, arr, ub As Long, r As Long, r1 As Long Dim c As Range Set d = CreateObject("scripting.dictionary") arr = rng.Value r1 = rng.Cells(1).Row ub = UBound(arr, 1) For r = 1 To ub v = arr(r, 1) If Len(v) > 0 Then If d.exists(v) Then d(v) = d(v) & "|" & r1 + (r - 1) Else d.Add v, r1 + (r - 1) End If End If Next r Set GetMap = d End Function 

• Convertir l'image (jpg) en base64 dans Excel VBA?
• Comparaison de deux colonnes et return d'une cellule adjacente spécifique dans Excel
• Protéger le code dans un classur Excel?
• Changer la couleur de la police pour un char spécifique dans une gamme de cellules
• Trouver par programme la version Excel d'un file Excel