I am looking for a way to mirror cells on different sheets. I have experimented with code that looks at a range and updates the range on another sheet upon a change, but I run into problems when the second sheet is sorted. The data copied to the second sheet is now out of order due to the sort.
Sheet 1
Column A - ID Number
Column B - Milestone Stage
Columns C+ contains data/columns that is not on Sheet 2
Sheet 2
Column A - Uses a formula to copy the ID number from Sheet 1 - this allows new entries to automatically appear
Column B- Milestone Stage
Columns C+ contains data/columns that is not on Sheet 1
So Column A & B is the same on both sheets. So initially, without making any data sorts, copying the entire range (A:B) to Sheet 2 would work. But, once I sort either sheet, copying a range messes up the sheets. This is due to the "other data" columns. Those columns are not mirrored on both sheets and a simple copying of the range will get that other data out of order.
What I am looking for is code that if a cell in column B changes on Sheet 1 ---> the code finds the ID number (in Column A) on the row that contains the cell that was changed ---> then takes that ID number to Sheet 2 and finds the row that contains that ID number ---> then copies the new Milestone Stage (from Sheet1 column B that was changed) to Column B.
VLOOKUP is not an option because I want to repeat the same VBA code in Sheet 2 so that Column B is mirrored. Thus, changing the Milestone Stage in either sheet updates the Milestone Stage in the other.
Any help would be greatly appreciated.
Sheet 1
Column A - ID Number
Column B - Milestone Stage
Columns C+ contains data/columns that is not on Sheet 2
Sheet 2
Column A - Uses a formula to copy the ID number from Sheet 1 - this allows new entries to automatically appear
Column B- Milestone Stage
Columns C+ contains data/columns that is not on Sheet 1
So Column A & B is the same on both sheets. So initially, without making any data sorts, copying the entire range (A:B) to Sheet 2 would work. But, once I sort either sheet, copying a range messes up the sheets. This is due to the "other data" columns. Those columns are not mirrored on both sheets and a simple copying of the range will get that other data out of order.
What I am looking for is code that if a cell in column B changes on Sheet 1 ---> the code finds the ID number (in Column A) on the row that contains the cell that was changed ---> then takes that ID number to Sheet 2 and finds the row that contains that ID number ---> then copies the new Milestone Stage (from Sheet1 column B that was changed) to Column B.
VLOOKUP is not an option because I want to repeat the same VBA code in Sheet 2 so that Column B is mirrored. Thus, changing the Milestone Stage in either sheet updates the Milestone Stage in the other.
Any help would be greatly appreciated.