Copy and paste based on found cells using two spreadsheets with dynamic ranges

**shawnsonline** · 04-28-2012, 09:58 AM

I have been working on this for a very long time and cannot find a solution. I will post examples below.
I have a main spreadsheet that spans from column A to column E.
I have a macro that uses my drop down box in C2 to find a value in column C and change all matching values below it to whatever I enter in D2.
What happens is that from time to time additional data will be imported starting at the next available cell, and sometimes that data will have the same value as the value I previously changed. I am trying to find a way to automatically check it there is an "old" value in the new import and change the old value to the new value. I thought the only way to do this to create a second spreadsheet that shows the "old" values in column A and the "changed" value in column B and then have it find and replace the new import off of that. The second spreadsheet would grow then based on the amount of values that I change initially so there I assume there would need to be a loop so it automatically searches for all values that need to change.

Example
Beginning Values

(My drop down box user selects B) (Change to AA)
A
B
C
B
E

Altered Values

(My drop down box) (Changed value empty)
A
AA
C
AA
E

Now My Problem

(My drop down box) (Changed value empty)
A
AA
C
AA
E
Later I import more data.
F
G
B This value I would like to automatically change to "AA"
I thought i needed add a spreadsheet with the old and new values that the "auto change" would need to reference.

Hopefully I explained this well but I am not sure. Thank you in advance.

**arlu1201** · 04-28-2012, 12:20 PM

Please attach the sample files.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**shawnsonline** · 04-28-2012, 02:49 PM

Thank you so much for your quick response. I am attaching a sample of what I am looking for. To better explain off the sample. When entering text in D2 of "Animal Backround Sorter" tab and press the button it will copy the old and new data to next available cell in L and M columns and then find and replace the values. All of that works well. But lets say later we import the list on the "Imported data" tab. How can I automatically have it make the changes in the L and M columns? Please keep in mind that Land M column continues to grow as more and more animals get changed. Maybe we do not need the L and M columns but from everything I have tried it seems as though I need to keep a running list with all the changes to refer to. I appreciate everything!