I must start off by saying, I have no VBA experience at all so I don’t have a sample VBA code to start from. I am grateful for all of you VBA experts out there and I’m hopeful that someone might be able to help me out.
I’m not sure if this is possible with a single VBA or if it may need to be split up into two separate commands. Either way is fine.
To start with, I have two workbooks. Workbook A (ThisWorkbook), Sheet 27 (OTU), contains two lists. List in Range B:B are the values to be found and matched, list in Range C:C is the replacement values if the match is found. In addition, cell $F$14 contains the directory of the file, cell $F$15 contains the file name, and cell $F$16 contains the Sheet Name of the second workbook.
Second Workbook B(Any name as defined in $F$15 of Workbook A), Sheet 2(Any name as defined in cell $F$16 of Workbook A), contains a list in Range A:A with values to be matched and also a blank range G:G where to preserve the original value from Range A:A.
When comparing the values from Workbook A, Sheet 27, Range B:B with Workbook B, Sheet 2, Range A:A, if match is found, I would like to preserve the original value from the Workbook B, Sheet 2, Range A:A and copy it to a corresponding cell in the same row in Range G:G of Workbook B, Sheet 2. At the same time, I would like to replace the original value in Workbook B, Sheet 2, Range A:A with a replacement value from the Workbook A, Sheet 27, Range C:C, if the match is found.
I would like to use a dynamic option for the Workbook A (ThisWorkbook) so that if the workbook is copied the VBA would still work without having to change the code for every new Workbook A Copy.
Any help is greatly appreciated!!!
I’m not sure if this is possible with a single VBA or if it may need to be split up into two separate commands. Either way is fine.
To start with, I have two workbooks. Workbook A (ThisWorkbook), Sheet 27 (OTU), contains two lists. List in Range B:B are the values to be found and matched, list in Range C:C is the replacement values if the match is found. In addition, cell $F$14 contains the directory of the file, cell $F$15 contains the file name, and cell $F$16 contains the Sheet Name of the second workbook.
Second Workbook B(Any name as defined in $F$15 of Workbook A), Sheet 2(Any name as defined in cell $F$16 of Workbook A), contains a list in Range A:A with values to be matched and also a blank range G:G where to preserve the original value from Range A:A.
When comparing the values from Workbook A, Sheet 27, Range B:B with Workbook B, Sheet 2, Range A:A, if match is found, I would like to preserve the original value from the Workbook B, Sheet 2, Range A:A and copy it to a corresponding cell in the same row in Range G:G of Workbook B, Sheet 2. At the same time, I would like to replace the original value in Workbook B, Sheet 2, Range A:A with a replacement value from the Workbook A, Sheet 27, Range C:C, if the match is found.
I would like to use a dynamic option for the Workbook A (ThisWorkbook) so that if the workbook is copied the VBA would still work without having to change the code for every new Workbook A Copy.
Any help is greatly appreciated!!!
