Excel VBA - Copy data to another workbook with match
Hello Experts,
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.
For Example:
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 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.
Thanks,
Michael
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.
For Example:
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 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.
Thanks,
Michael
ASKER
Experts,
Attached are 2 workbooks. 1 being the sourcewb and 2nd being the destination workbook.
I hope this helps in getting to where I need to be.
Thank you,
Michael
Book1.xlsm
Book2.xlsm
Attached are 2 workbooks. 1 being the sourcewb and 2nd being the destination workbook.
I hope this helps in getting to where I need to be.
Thank you,
Michael
Book1.xlsm
Book2.xlsm
Thanks, Michael.
Michael,
I reckoned that you'd go blind clicking through thousands of pairs of entries, so I came up with what I think is an improvement. Instead of displaying matched pairs, the macro displays a form showing the Product that's being matched and all of its first and second word matches. The user then double-clicks on the required entry and the next Product is displayed.
Some points...
(1) As this is currently just a demo, I have not done any of the code around flagging entries as selected. Instead I just output the match details to a Log sheet. (The final version would, beside flagging matching, subsequently ignore matched entries.)
(2) Besides the Products' Names, I also show their Row No. and their Unit (not much point in matching a Gallon with a Case).
(3) Click on the Blue "Display Dialog" to start everything.
Give it a go and let me know what you think of the idea of using a form.
(BTW, it might make your task easier if you standardised all of the Units (e.g. change all "LBS" to "LB"). The macro could then reject any entries whose Names matched, but not their Units. This would make it much easier to spot the actual matching entry.)
Regards,
Brian.Book2-Dest-V3.xlsm
I reckoned that you'd go blind clicking through thousands of pairs of entries, so I came up with what I think is an improvement. Instead of displaying matched pairs, the macro displays a form showing the Product that's being matched and all of its first and second word matches. The user then double-clicks on the required entry and the next Product is displayed.
Some points...
(1) As this is currently just a demo, I have not done any of the code around flagging entries as selected. Instead I just output the match details to a Log sheet. (The final version would, beside flagging matching, subsequently ignore matched entries.)
(2) Besides the Products' Names, I also show their Row No. and their Unit (not much point in matching a Gallon with a Case).
(3) Click on the Blue "Display Dialog" to start everything.
Give it a go and let me know what you think of the idea of using a form.
(BTW, it might make your task easier if you standardised all of the Units (e.g. change all "LBS" to "LB"). The macro could then reject any entries whose Names matched, but not their Units. This would make it much easier to spot the actual matching entry.)
Regards,
Brian.Book2-Dest-V3.xlsm
ASKER
Hi Awesome Brian,
This is definately going to be a benefit. There are just a minor few points to help me to streamline the process before you move forward.
1. On UserForm1 - you can omit the Unit Column. My Import wb that is generated weekly will add the unit of measure and cost value. If it is does match in the current form it is a non-issue.
2. On the Search/Match - Can you not have it search the Breakfast category (Anything after row 271) There will never be a match from the source wb to the destination wb since this is all new data for future use. It will help eliminate unnecessary items populating in the UserForm Box.
Besides these 2 items. I think this is more than a demo - it resolves the bulk of the issues. I am hoping that the copying of data from columns "D:I" will be a little less involved than the all the work that you have done already.
I truly appreciate you taking this on and teaching me along the way as I analyze your code.
Michael
This is definately going to be a benefit. There are just a minor few points to help me to streamline the process before you move forward.
1. On UserForm1 - you can omit the Unit Column. My Import wb that is generated weekly will add the unit of measure and cost value. If it is does match in the current form it is a non-issue.
2. On the Search/Match - Can you not have it search the Breakfast category (Anything after row 271) There will never be a match from the source wb to the destination wb since this is all new data for future use. It will help eliminate unnecessary items populating in the UserForm Box.
Besides these 2 items. I think this is more than a demo - it resolves the bulk of the issues. I am hoping that the copying of data from columns "D:I" will be a little less involved than the all the work that you have done already.
I truly appreciate you taking this on and teaching me along the way as I analyze your code.
Michael
Michael,
Thanks for the kind words!
A few queries...
(1) you can omit the Unit Column
On last pitch on this - when there are more than two or three potential matches, I found the Unit a quick way to zoom in on the most likely matches.
(2) Can you not have it search the Breakfast category (Anything after row 271)
If it's OK, I'll be lazy and use the row no.
(3) I am hoping that the copying of data from columns "D:I" will be a little less involved
A lot!
(4) No cell has more than 4 words in it's description.
I just noticed this in your original question. I'm not using this information, is that correct?
(5) It wasn't the reason I created it, but I could use the Log so that the updates don't happen until you click on the "Finished" button (used to be "Cancel"). So, you could then choose to implement the partial updates or else drop them completely.
(6) My initial read was that this was a once-off exercise. However, it's now looking like a weekly job. If that's correct then there are much better ways to do this...
(A) Do this manual exercise for each Store.
(B) For each Store, there will then be a Log sheet holding the matching details.
(C) In future, each Store's details can automatically update by using the matching details.
Regards,
Brian.
Thanks for the kind words!
A few queries...
(1) you can omit the Unit Column
On last pitch on this - when there are more than two or three potential matches, I found the Unit a quick way to zoom in on the most likely matches.
(2) Can you not have it search the Breakfast category (Anything after row 271)
If it's OK, I'll be lazy and use the row no.
(3) I am hoping that the copying of data from columns "D:I" will be a little less involved
A lot!
(4) No cell has more than 4 words in it's description.
I just noticed this in your original question. I'm not using this information, is that correct?
(5) It wasn't the reason I created it, but I could use the Log so that the updates don't happen until you click on the "Finished" button (used to be "Cancel"). So, you could then choose to implement the partial updates or else drop them completely.
(6) My initial read was that this was a once-off exercise. However, it's now looking like a weekly job. If that's correct then there are much better ways to do this...
(A) Do this manual exercise for each Store.
(B) For each Store, there will then be a Log sheet holding the matching details.
(C) In future, each Store's details can automatically update by using the matching details.
Regards,
Brian.
Michael,
As I understand your requirements, the attached does everything - except ignore Destination entries with data in columns D:I. I'm waiting on your answer to (6) above before proceeding with that.
Edit: Oops, the "Yellow" code was upside down. Now corrected.
Regards,
Brian.Book2-Dest-V4.xlsm
As I understand your requirements, the attached does everything - except ignore Destination entries with data in columns D:I. I'm waiting on your answer to (6) above before proceeding with that.
Edit: Oops, the "Yellow" code was upside down. Now corrected.
Regards,
Brian.Book2-Dest-V4.xlsm
ASKER
Hi Brian,
Once I have realigned everything correctly with your code. There will no need to run this every week. I will have Column A,B,C and L locked at all times. My import will run weekly to import updated data for these columns, and any other changes and import data.
If a location gets out of wack for some reason - I can use this as a ad-hoc routine to realign things back to the way they were before.
I have not downloaded version 4 yet - but I will start on it next.
Your hard work, ingenuity and committment to assist is very much appreciated,
Michael
Once I have realigned everything correctly with your code. There will no need to run this every week. I will have Column A,B,C and L locked at all times. My import will run weekly to import updated data for these columns, and any other changes and import data.
If a location gets out of wack for some reason - I can use this as a ad-hoc routine to realign things back to the way they were before.
I have not downloaded version 4 yet - but I will start on it next.
Your hard work, ingenuity and committment to assist is very much appreciated,
Michael
ASKER
Hi Brian,
Things are working great thus far - however, there are a few lines of code that need to be changed to avoid it from bugging out.
On the Sub UserForm_Initialize()
The xSrce_Book will be the same path as the "This Workbook" and will already be open. Or Windows (2) however you want to look at it.
The xSrce_Book name is "NEW Foodcost Workbook" - it may be a .xls or .xlsm but the final wb that I save and use will be .xlsm format.
Also
In the xSrce_Book - there is no wsheet called "INVENTORY". I have 2 possible sheets that could be the Source worksheet. I am using the active sheet of the 2 and using that one as the Source. My new workbook does away with the 2 sheets and now uses 1 - called "INVENTORY".
The code to identify which sheet to use would look something like this:
If xSrce_Book.Worksheets("DAS
xSrce_Book.Worksheets("SMP
xSrce_Last = ActiveSheet.Range("A1").Sp
Set xCell = ActiveSheet.Range("A6")
Else
xSrce_Book.Worksheets("750
xSrce_Last = ActiveSheet.Range("A1").Sp
Set xCell = ActiveSheet.Range("A6")
End If
I am not sure where to modify your code to add this if statement and what exactly to remove to get it to identify the xSrce_Book without the Application.GetOpenFilenam
Thanks,
Michael
Things are working great thus far - however, there are a few lines of code that need to be changed to avoid it from bugging out.
On the Sub UserForm_Initialize()
The xSrce_Book will be the same path as the "This Workbook" and will already be open. Or Windows (2) however you want to look at it.
The xSrce_Book name is "NEW Foodcost Workbook" - it may be a .xls or .xlsm but the final wb that I save and use will be .xlsm format.
Also
In the xSrce_Book - there is no wsheet called "INVENTORY". I have 2 possible sheets that could be the Source worksheet. I am using the active sheet of the 2 and using that one as the Source. My new workbook does away with the 2 sheets and now uses 1 - called "INVENTORY".
The code to identify which sheet to use would look something like this:
If xSrce_Book.Worksheets("DAS
xSrce_Book.Worksheets("SMP
xSrce_Last = ActiveSheet.Range("A1").Sp
Set xCell = ActiveSheet.Range("A6")
Else
xSrce_Book.Worksheets("750
xSrce_Last = ActiveSheet.Range("A1").Sp
Set xCell = ActiveSheet.Range("A6")
End If
I am not sure where to modify your code to add this if statement and what exactly to remove to get it to identify the xSrce_Book without the Application.GetOpenFilenam
Thanks,
Michael
Michael,
(1) The xSrce_Book will be the same path as the "This Workbook" and will already be open. Or Windows (2) however you want to look at it.
The xSrce_Book name is "NEW Foodcost Workbook" - it may be a .xls or .xlsm but the final wb that I save and use will be .xlsm format.
So it should check for an open file called either "NEW Foodcost Workbook.xls" or ""NEW Foodcost Workbook.xlsm" and use that as xSrce_Book? Assuming this is correct, do you have a preference for which I should check first - .xls or .xlsm?
(2) In the xSrce_Book - there is no wsheet called "INVENTORY". I have 2 possible sheets that could be the Source worksheet. I am using the active sheet of the 2 and using that one as the Source.
Having identified xSrce_Book, I should use its active sheet (ignoring any other ones) - correct?
(3) My new workbook does away with the 2 sheets and now uses 1 - called "INVENTORY".
Sorry, I don't understand this. Do I need to?!
(4) On the one hand you appear to say that I should use the active sheet in xSrce_Book, but your code seems to suggest that I should instead use the content of a sheet called "DASHBOARD" to select a sheet called either "SMP-3Way" or "7500-3Way".
So, should I use the active sheet or select one based on "DASHBOARD" content?
(Either way, this feels very fragile. For example, what opens the file to be used as xSrce_Book? Ideally, this macro should be tied into that so there's no ambiguity.)
Thanks,
Brian.
(1) The xSrce_Book will be the same path as the "This Workbook" and will already be open. Or Windows (2) however you want to look at it.
The xSrce_Book name is "NEW Foodcost Workbook" - it may be a .xls or .xlsm but the final wb that I save and use will be .xlsm format.
So it should check for an open file called either "NEW Foodcost Workbook.xls" or ""NEW Foodcost Workbook.xlsm" and use that as xSrce_Book? Assuming this is correct, do you have a preference for which I should check first - .xls or .xlsm?
(2) In the xSrce_Book - there is no wsheet called "INVENTORY". I have 2 possible sheets that could be the Source worksheet. I am using the active sheet of the 2 and using that one as the Source.
Having identified xSrce_Book, I should use its active sheet (ignoring any other ones) - correct?
(3) My new workbook does away with the 2 sheets and now uses 1 - called "INVENTORY".
Sorry, I don't understand this. Do I need to?!
(4) On the one hand you appear to say that I should use the active sheet in xSrce_Book, but your code seems to suggest that I should instead use the content of a sheet called "DASHBOARD" to select a sheet called either "SMP-3Way" or "7500-3Way".
So, should I use the active sheet or select one based on "DASHBOARD" content?
(Either way, this feels very fragile. For example, what opens the file to be used as xSrce_Book? Ideally, this macro should be tied into that so there's no ambiguity.)
Thanks,
Brian.
ASKER
Good Day Brian,
I hope you had a nice night.
1) Yes, you are correct that it should check for either of those 2 workbooks. If it checks the for 2010 version first then that is fine. .xlms.
I think I muddied the water too much with what I stated. Let me try to clarify for points 2 - 4
In both versions of the Foodcost Workbook.xls* there are 2 worksheets called "SMP-3Way" and "7500-3Way". Depending on the value of of Worksheets("DASHBOARD").Ra
After I have imported data into the sheet called "INVENTORY' - those 2 sheets will be useless since they have been replaced with 1 sheet called "INVENTORY".
The key elements are that the source workbook will be either:
"NEW FoodCost Workbook.xls" or "NEW FoodCost Workbook.xlsm".
The source workbook will already be open and residing in the same directory as the destination workbook where I will be placing this code you have written. (Perhaps the macro should be tied to look in the same directory where the destination workbook is located and look for 1 of the 2 named workbooks. If it can not find either, it exits sub.
The other key element is the one I drew reference to in the beginning of this post - That the source worsheet is either named "7500-3Way" or "SMP-3Way". And that is contingent on value of of Worksheets("DASHBOARD").Ra
As I stated I may have muddied the water too much in my previous post - and I do apologize for that.
I hope this clears up the misunderstanding.
Respectfully,
Michael
I hope you had a nice night.
1) Yes, you are correct that it should check for either of those 2 workbooks. If it checks the for 2010 version first then that is fine. .xlms.
I think I muddied the water too much with what I stated. Let me try to clarify for points 2 - 4
In both versions of the Foodcost Workbook.xls* there are 2 worksheets called "SMP-3Way" and "7500-3Way". Depending on the value of of Worksheets("DASHBOARD").Ra
After I have imported data into the sheet called "INVENTORY' - those 2 sheets will be useless since they have been replaced with 1 sheet called "INVENTORY".
The key elements are that the source workbook will be either:
"NEW FoodCost Workbook.xls" or "NEW FoodCost Workbook.xlsm".
The source workbook will already be open and residing in the same directory as the destination workbook where I will be placing this code you have written. (Perhaps the macro should be tied to look in the same directory where the destination workbook is located and look for 1 of the 2 named workbooks. If it can not find either, it exits sub.
The other key element is the one I drew reference to in the beginning of this post - That the source worsheet is either named "7500-3Way" or "SMP-3Way". And that is contingent on value of of Worksheets("DASHBOARD").Ra
As I stated I may have muddied the water too much in my previous post - and I do apologize for that.
I hope this clears up the misunderstanding.
Respectfully,
Michael
ASKER
Ouch - I stated directory. But what I meant to say was folder. The source workbook and destination workbook will be in the same folder. And nothing else will be in that folder.
If the code identifies the path of the destination workbook (the workbook that holds this code) then it will find one of the 2 other possible workbooks. The one named "NEW FoodCost WorkBook.xls" or the one with the .xlms extention.
You may have know what I meant when I said directory - but I wanted to clarify since I think I already caused you some stress.
M.
If the code identifies the path of the destination workbook (the workbook that holds this code) then it will find one of the 2 other possible workbooks. The one named "NEW FoodCost WorkBook.xls" or the one with the .xlms extention.
You may have know what I meant when I said directory - but I wanted to clarify since I think I already caused you some stress.
M.
Michael,
The source workbook will already be open and residing in the same directory as the destination workbook where I will be placing this code you have written. (Perhaps the macro should be tied to look in the same directory where the destination workbook is located and look for 1 of the 2 named workbooks. If it can not find either, it exits sub.
(A) As the Source file is already open, I will delete the code that prompts the user for the file name. Correct?
(B) If the Source and Destination workbook are already open, why do I care what their paths are?
Thanks,
Brian.
The source workbook will already be open and residing in the same directory as the destination workbook where I will be placing this code you have written. (Perhaps the macro should be tied to look in the same directory where the destination workbook is located and look for 1 of the 2 named workbooks. If it can not find either, it exits sub.
(A) As the Source file is already open, I will delete the code that prompts the user for the file name. Correct?
(B) If the Source and Destination workbook are already open, why do I care what their paths are?
Thanks,
Brian.
ASKER
Greetings Brian,
(A) As the Source file is already open, I will delete the code that prompts the user for the file name. Correct?
Yes this is correct.
(B) If the Source and Destination workbook are already open, why do I care what their paths are?
I included this information since you had previously stated it seemed fragile and/or ambiguous - but as I review your comments I see that you were talking about the worksheet and not the workbook. My comments about the path were not needed. Please ignore them.
Michael
(A) As the Source file is already open, I will delete the code that prompts the user for the file name. Correct?
Yes this is correct.
(B) If the Source and Destination workbook are already open, why do I care what their paths are?
I included this information since you had previously stated it seemed fragile and/or ambiguous - but as I review your comments I see that you were talking about the worksheet and not the workbook. My comments about the path were not needed. Please ignore them.
Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outstanding! Everything worked perfectly.
I really appreciate the time and effort of Brian "redmondb" to assist me. I feel he went above and beyond the call of duty on this one. He is very kind and patient.
I really appreciate the time and effort of Brian "redmondb" to assist me. I feel he went above and beyond the call of duty on this one. He is very kind and patient.
Thanks, Michael!
Things will go much faster if you can provide us with a couple of sample file.
Thanks,
Brian.