Hi All, First post here. I have been searching for a while for some VBA code to achieve what i need but i have not had any luck yet.
I have two workbooks WB1 and WB2
Each workbook has the date with Column A
I need the VBA code to find a match of the date in Column A within WB1 and WB2
If a match is found, then the values within the matched date row, Column (B:E) should be copied from WBK1 to WB2 Column (C:F) within the corresponding matched date row.
The only thing I have managed to find that matches cells and copies data is the below code.
However this
- Only copes the Value in 1 column where I need a range.
-Only copies data within the same workbook
-Takes a while for the data to populate (maybe as I have over 9000 lines of data)
Any assistance would be much appreciated. Thank you!
Sub COPY_DATA_FORECAST()
Dim Cl As Range
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
'Searches date range within Column A and copy data from next column
With Sheets("sheet1")
For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Cl.Offset(, 1).Value
Next Cl
End With
With Sheets("Sheet2")
For Each Cl In .Range("A20", .Range("A" & Rows.Count).End(xlUp))
If Dic.exists(Cl.Value) Then Cl.Offset(, 2).Value = Dic(Cl.Value)
Next Cl
End With
End Sub
I have two workbooks WB1 and WB2
Each workbook has the date with Column A
I need the VBA code to find a match of the date in Column A within WB1 and WB2
If a match is found, then the values within the matched date row, Column (B:E) should be copied from WBK1 to WB2 Column (C:F) within the corresponding matched date row.
The only thing I have managed to find that matches cells and copies data is the below code.
However this
- Only copes the Value in 1 column where I need a range.
-Only copies data within the same workbook
-Takes a while for the data to populate (maybe as I have over 9000 lines of data)
Any assistance would be much appreciated. Thank you!
Sub COPY_DATA_FORECAST()
Dim Cl As Range
Dim Dic As Object
Set Dic = CreateObject("scripting.dictionary")
'Searches date range within Column A and copy data from next column
With Sheets("sheet1")
For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Cl.Offset(, 1).Value
Next Cl
End With
With Sheets("Sheet2")
For Each Cl In .Range("A20", .Range("A" & Rows.Count).End(xlUp))
If Dic.exists(Cl.Value) Then Cl.Offset(, 2).Value = Dic(Cl.Value)
Next Cl
End With
End Sub