Auto populate from master file to other [Solved]

Posts
4
Registration date
Monday March 26, 2018
Last seen
April 18, 2018
-
Hi
Good afternoon!!

I have a master data file that comprises of data of overall activities of the project including sales,contract value, work done etc etc. I want to have a separate excel file for separate sales person. Kindly help me to do that. I need to edit the masterlist and the other files has to be updated automatically. Pls help me out.
See more 

Your reply

2 replies

Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
0
Thank you
Hi Mohamed,

Why would you want separate files, when you could have just 1 with multiple tabs? It makes data processing a lot more convenient.


Best regards,
Trowa
MohamedGhouse
Posts
4
Registration date
Monday March 26, 2018
Last seen
April 18, 2018
-
Hi Trowa

Thanks for your reply. Actually I have details of all person in sales department in my master file that will be shared to management but I want separate file that has to be shared to concern sales person. One sales person doesn't need to view the details of others. From single master file i need to split up in to 8 sales person individually. Kindly help me to do this.

Thanks in advance. Awaiting for your reply.
TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
Hi Mohamed,

Could you post some sample data?, so I can figure out what to do.

Also what do you mean by update automatically? Do you mean new rows, or changes made to existing rows (then how would Excel recognise the existing row?)?

Best regards,
Trowa
MohamedGhouse
Posts
4
Registration date
Monday March 26, 2018
Last seen
April 18, 2018
-
Hi TrowaD

Please find the details below

File No NAME CONTRACTOR VALUE SALES REP

1909 AL BAR EMIRATES CONTG CO 96,000.00 FA
1910 BURJ AL GHANDI & CCC 512,000.00 NJ
1918 BLDG J/ALI AIC CONTG 36,000.00 RB
1923 B-BAY UNEC 125,000.00 NJ
1930 B BAY UNEC 115,000.00 FA

The master file contains data of all sales representative. I need to have separate file for separate sales person. If I update the master file the individual sales has to be updated automatically.

Thanks in advance. Please suggest.

Ghouse
Respond to TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
0
Thank you
Hi Ghouse,

Next time I will work out the changes. For now I got a code to distribute all rows to it's respective sheet. When the sheet doesn't exist, one will be created.

Sub RunMe1Time()
Dim x As Integer

For Each cell In Range("E2:E" & Range("A1").CurrentRegion.Rows.Count)
    cell.EntireRow.Copy
    If Not SheetExists(cell.Value) Then
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = cell.Value
    End If
    Sheets(cell.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Next cell
End Sub

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet

If Len(Sheets(SheetName).Name) > 0 Then
    SheetExists = True
    Exit Function
End If

NoSuchSheet:
End Function


Put the code in a standard module.

Let me know how that works out.

Best regards,
Trowa
MohamedGhouse
Posts
4
Registration date
Monday March 26, 2018
Last seen
April 18, 2018
-
Dear TrowaD

Good morning!!

Thank you so much for your response. It works good.

Mohamed Ghouse.
TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
Awesome, thanks for the feedback!
Respond to TrowaD