Same Column on Two Sheets (Auto-update / match)?

[sarah]

Hi. I have two sheets in the same workbook. I need a column on the 2nd
sheet to match a column on the 1st sheet. So that when data is entered into
this column on sheet1 it is also entered automatically on sheet2. Is this
possible? I tried to set the column on sheet2 to =Sheet1!A1, but when I
delete/add rows to sheet1 it does not update sheet2.

Any advice would be greatly appreciated! Thank you.

 

[Kevin B]

Your formula is fine but when you need to insert a row do the following so
that it occurs in both worksheets:

1. While holding down the CTRL key, click on the tabs for sheets 1 & 2
2. Select the row where you want to insert the new row
3. Click INSERT on the menu and select ROWS
4. Right click on either of the selected worksheet tabs
5. Select Ungroup sheets

You'll have to copy the formula above the newly inserted row in sheet 2

Hope this helps

 

[sarah]

Is there a simpler way to do it? A few different people will be working with
the document, and I can see it easily getting messed-up. If there's a way to
ensure that both columns match, it would be preferred.

Thanks again.

 

[Kevin B]

Aside from writing a macro to perform this operation, I don't know of any
other way of automating the process. It's either manual or macro driven.

 

[JLatham]

Sarah, here's some code that would keep them sync'd although you may now get
comments about the workbook containing macros (code).

Put this routine into the second worksheet's code segment and change the
name of the source sheet in it. Then anytime the second sheet is chosen, the
code will automatically set up column C (you can change that as required
also) to be a mirror image of the designated column on the 'source' sheet.
However, until this is actually done, then they may be out of sync which
could affect formulas on other sheets that reference the information on this
second/copy sheet in this column.

To put the code in place, choose the sheet that needs to match the source
sheet and right-click on the sheet's name tab and select [View Code]. Copy
the code below and paste it into the code module; make changes to the sheet
name and column reference as needed. Close the VB Editor. Try it out - make
some changes on the source sheet, select the copy sheet and see that they
appear on it properly.

Private Sub Worksheet_Activate()
'assumes you want column C on this sheet
'to mirror the contents of column C on
'sheet named "Sheet1" in this workbook
'any time this sheet is selected, the
'contents of C on Sheet1 will be copied
'into column C of this sheet

Dim sourceSheet As Worksheet
Dim copyRange As Range
Dim whereAmI As Range

Set whereAmI = Selection
'change sheet name as required
Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
'change "C1:" and "C" as required
Set copyRange = sourceSheet.Range("C1:" & _
sourceSheet.Range("C" & Rows.Count).End(xlUp).Address)
copyRange.Copy
Range("C1").PasteSpecial ' change "C1" as required.
whereAmI.Activate
Set whereAmI = Nothing
Set copyRange = Nothing
Set sourceSheet = Nothing
Application.CutCopyMode = False
End Sub