Creating Mirrored Cells on different sheets based on reference column

tutrup

New Member
Joined
Jan 30, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

tutrup

New Member
Joined
Jan 30, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
To clarify my request:
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 data from Sheet1 column B ---> to Column B (Sheeet2).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top