How to Mirror cells in excel 2013

Question

I want to make an excel file for sales management of my company. the excel I have created has 4 sheets and every sheet is handled by different persons.

I want to dedicate the first column of these sheets to the status of each order in way that when someone changes the status in sheet number one the status column in other 3 sheets change too. Also when another person changes the status in sheet number 2 the sheets number 1,3 and 4 change too.

Is it possible? If so, how can I do it?

Solution

Put this code in Sheet1 Module (Right Mouse Click Sheet1 Tab and select "View Code"):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rFrom as Range

If Target.Count = 1 Then
    With Target.WorkSheet
         Set rFrom = .Range("A:A")
         If Not Intersect(Target, rFrom) Is Nothing Then
             Application.EnableEvents = False
             'Include next line Just in Case something happens
             '    You don't want to leave EnableEvents off
             On Error Resume Next
             rFrom.Copy Worksheets("Sheet2").Range("A:A")
             If Err.Number <> 0 Then
                 Msgbox "Error Occurred"
             End If
             Application.EnableEvents = True
         End If
     End With
End If
End Sub

Put the same for Sheet2 (obviously changing the appropriate addresses). Change Sheet2 to Sheet1

For changing only one sheet:
=Sheet1!A:A if you want that cells in another sheet mirror cells in column A in Sheet1.

Also another question asked on superuser is similar to yours but not the same.

Comments

[CharlieRB]

Welcome to Super User. Please edit your question to include what you have tried / researched to resolve this and where you got stuck. We are happy to help, but there is a reasonable expectation you have attempted something on your own first.

[CharlieRB]

@EricF Simply posting a comment like this doesn't help much. Care to post an answer how to do that?

[Ehsan Ghabchi]

tnx for your advice but could you help me a little more, I know nothing about coding.

[Davidenko]

Just copy the code above as explained by right clickingand selecting view code on the sheet1 and on sheet2 copy the same code and change code (Sheet2 to Sheet1) as explained in the answer and it will work. You can do the same for other sheets!

[Davidenko]

That only works for column A!

[Davidenko]

Let me know if you have any problems!

[Ehsan Ghabchi]

Davidenko I copied the code but how should I change the sheet names in this code? my workbook has 6 sheets, what should I put in sheets 3 to 6 instead of ("Sheet2")

[Davidenko]

In every sheet put codes for other sheets! For example for sheet3: rFrom.Copy Worksheets("Sheet1").Range("A:A") rFrom.Copy Worksheets("Sheet2").Range("A:A") rFrom.Copy Worksheets("Sheet4").Range("A:A") rFrom.Copy Worksheets("Sheet5").Range("A:A") rFrom.Copy Worksheets("Sheet6").Range("A:A")

[Davidenko]

Tools -> Macro -> Security Click on Low @EhsanGhabchi

[Davidenko]

uploadmb.com/dw.php?id=1421272892 I have uploaded the excel file. But set macro security to low or it won't work! @EhsanGhabchi

[Ehsan Ghabchi]

OMG... tnx bro... you're the best