Auto populate from master file to other [Solved/Closed]

Report
Posts
4
Registration date
Monday March 26, 2018
Status
Member
Last seen
April 18, 2018
-
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
-
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.

2 replies

Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
Status
Member
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
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
Status
Member
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
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
Status
Member
Last seen
April 18, 2018

Dear TrowaD

Good morning!!

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

Mohamed Ghouse.
TrowaD
Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
Awesome, thanks for the feedback!