Excel VBA - Copy data to another workbook with match
Posted on 2012-08-24
I have an old workbook and a new book that I am trying to move data from one to the other.
In the past I have used:
For Each c In Sheet1.UsedRange - Copy. But the circumstances are very different.
I have a new base workbook - but the order of data has changed and the nomenclatures have been modified so I can not directly import (copy) without a match scenerio. Where my issue lies is that I need to match the old nomenclature to the new nomenclature and then copy cells with a .offsets/resize. The new workbook will not allow end-users to change the names or move the data row - hence I need to get all 13 users on the same format.
All data resides in Column A that I need to match from wb1 to wb2 then copy.
The difficult part or time consuming part is that I do not want to look up each row and see if it is a match then hand-key the corresponding values. I am hoping that I could get some help in writing a routine that will speed up the process.
Somewhere in wb1.sheet1 column A has "APPLE GRANNY SMITH"
In wb2.sheet1 column A there is a "GRANNY SMITH APPLE"
The routine needs to take the first word of wb1 row 1 and loop through wb2 Range and find a match of "APPLE" to a cell in wb2 column A containing the word "APPLE". Then a msg box appears asking if this is a match ("Does APPLE GRANNY SMITH = GRANNY SMITH APPLE"), if yes then copy data, if no, then continue checking until the range has ended. There will some clicking to go through the loop, but it certainly will be easier than trying to do this manually. And then move to the next location and do the same.
The information to copy will be data in columns D,E,F,G,H and I
For example if:
If wb1.sheet1.Range("A7") is a match to wb2.sheet1.Range("A47") then
wb2.sheet1.Range("D47:I47") = wb1.sheet1.Range("D7:I7").value
wb1 Range is A6:A200
wb2 Range is A6:A300 (The extra 100 rows are there for future use, are blank and not in sequence)
No cell has more than 4 words in it's description.
Is there is a way for it to not check a row in wb2 if there is data in any column of D:I - since the loop already found a match and copied data into those cells. This way it will remove the redundancy of reasking a match in a row that has already been identified and copied?
The last part would be to printout anything that it could not find a match in wb2 or it could highlight the cell in wb1. column A with a solid pattern yellow color ( .Color = 65535) - this would make it easy to identify non copied data that will have to be hand-keyed.
If possible, if it did not find a match on the first word in wb2, that it would take the second word from wb1.column A and do one more loop before giving up.
I will be the most relieved and appreciative person on the planet if I could get this to happen!!
If I could award more than 500 points, I would.