0

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?

CharlieRB
  • 22,754
Ehsan Ghabchi
  • 23
  • 2
    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 Jan 14 '15 at 12:57
  • @EricF Simply posting a comment like this doesn't help much. Care to post an answer how to do that? – CharlieRB Jan 14 '15 at 15:28

1 Answers1

1

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.

Community
  • 1
Davidenko
  • 1,326
  • tnx for your advice but could you help me a little more, I know nothing about coding. – Ehsan Ghabchi Jan 14 '15 at 19:57
  • 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 Jan 14 '15 at 20:02
  • That only works for column A! – Davidenko Jan 14 '15 at 20:14
  • Let me know if you have any problems! – Davidenko Jan 14 '15 at 20:15
  • 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") – Ehsan Ghabchi Jan 14 '15 at 21:51
  • 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 Jan 14 '15 at 21:58
  • Tools -> Macro -> Security Click on Low @EhsanGhabchi – Davidenko Jan 14 '15 at 22:08
  • http://www.uploadmb.com/dw.php?id=1421272892 I have uploaded the excel file. But set macro security to low or it won't work! @EhsanGhabchi – Davidenko Jan 14 '15 at 22:11
  • 1
    OMG... tnx bro... you're the best – Ehsan Ghabchi Jan 18 '15 at 12:14