I want to copy an array from one workbook (wbML) to another (wbDB) using the dictionary method.
I want three columns of data from wbML: Ref, Name, Surname. It has many more columns of data and I can't always be certain where the data I want will be located.
I'm not certain that I'm adding the range correctly to the dictionary item.
Second, I would like to write the data to wbDB.
Any help would be appreciated.
Sub copy_range()
Dim headers As Variant, header As Variant, headerCol As Integer, wbDB As Workbook, wbML As Workbook, _
i As Integer, _
Dict1 As Scripting.Dictionary
Set Dict1 = New Scripting.Dictionary
Set wbDB = ThisWorkbook 'user initiates macro from wbDB
Set wbML = Workbooks("ML.xlsx")
headers = Array("ref", "name", "surname") 'wbML columns to copy
lr = Cells(Rows.Count, "A").End(xlUp).Row 'get last row
i = 1
wbML.Activate
For Each header In headers
headerCol = Rows("1").find(header).Column 'find header column
With Dict1
.item(i) = Range(Cells(1, headerCol), Cells(lr, headerCol)) 'Does this add the range to key.item?
End With
i = i + 1
Next header
'How do I write the dictionary items to wbDB?
End Sub
Bookmarks