Problem regarding Connection between different sheets of Same Excel File.

[Gocool]

Hello Experts,

I am having trouble to find the details as mentioned below:

Suppose this sheet as a Master sheet (sheet 1). & I have 4 other sheets i.e. AAA, BBB, CCC, DDD as per name of Company.
A B C D E F

SN	NAME	TRADE	EM CODE	Location	COMPANY
1	JOHN	CARPENTER	501	DOHA	AAA
2	JOHNNY	MASON	502	LUSAIL	BBB
3	SID	S-FIXER	503	DOHA	AAA
4	JAY	S-FIXER	504	BARWA	DDD
5	HARI	MASON	505	RAYYAN	CCC
6	JERRY	CARPENTER	506	DOHA	DDD
7	TOM	CARPENTER	507	DOHA	BBB
8	HARRY	MASON	508	RAYYAN	AAA
9	PETER	MASON	509	LUSAIL	CCC
10	JIM	S-FIXER	510	DOHA	AAA
11	JIMMY	MASON	511	RAYYAN	DDD

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col></colgroup><tbody></tbody>

(Sheet 2)

SN	NAME	TRADE	EMP CODE	LOCATION	COMPANY
?????????	?????????	?????????	?????????	?????????	BBB
?????????	?????????	?????????	?????????	?????????	BBB
?????????	?????????	?????????	?????????	?????????	BBB
?????????	?????????	?????????	?????????	?????????	BBB

<tbody>
</tbody>

I want to link this details to another sheet of same excel file with below mentioned terms.

1) The details in sheet 2 should be in reference of Company. i.e. sheet 2 will be AAA, Sheet 3 will be BBB, Sheet 4 will be CCC and so on.
2) If one cell is changed in master sheet (sheet 1) then it should be changed in its respective sheet also.
3) If the workers is transferred from AAA company to BBB Company then Sheet 2(AAA) should delete that worker from its list automatically and sheet 3 (BBB) should get it.

Sorry if i have some mistakes in Language.
I hope you will understand my problem and help me in this.

Thank You for your kind consideration and valuable time for help!

 

[Misca]

You didn't say if you prefer macro or formula, so I decided to come up with a little macro that
1) Clears all the other sheets but the Master sheet (=named "MasterSheet" in my code)
2) Loops through cells in Column F (MasterSheet) to find out the sheet name where it writes the row values

Sub CopyWorkersToCompanySheets()

Dim c As Range
Dim Rng As Range
Dim Headers As Range
Dim WS As Worksheet

Application.Screenupdating=False


'Loops through each worksheet:
For Each WS In Worksheets
    With WS
        If .Name <> "MasterSheet" Then  'If name doesn't match "MasterSheet"
            .Cells.Clear        'Clears the worksheet
        End If
    End With
Next WS


'MasterSheet
With Sheets("MasterSheet")
    Set Headers = .Range("A1:F1")   'Headers = A1:F1
    Set Rng = Range(.Cells(2, 6), .Cells(.Rows.Count, 6).End(xlUp)) 'F2:F & LastRow
End With


'Goes through each Company cell in MasterSheet:
    For Each c In Rng
        With Sheets(c.Value)    'The sheet name must match the cell value!
            With .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 6)
                If .Row = 2 Then
                    'First time in this sheet = Write the headers from MasterSheet
                    .Offset(-1).Value = Headers.Value
                End If
                    'Writes the values from c row:
                    .Value = c.Offset(, -5).Resize(, 6).Value
            End With
        End With
    Next c


End Sub

The code should do it as long as your sheet names are identical to company names in Column F.

 

[Gocool]

@ Misca Thanks alot for your help but i am completely unknown about macros. It will be better form me if you have any other suggestion .

Regards
Gocool