Auto populate from master file to other [Solved]

MohamedGhouse 4 Posts Monday March 26, 2018Registration date April 18, 2018 Last seen - Mar 26, 2018 at 06:06 AM - Latest reply: TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen
- Apr 19, 2018 at 10:46 AM
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

7 replies

TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Mar 27, 2018 at 12:08 PM
0
Helpful
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 4 Posts Monday March 26, 2018Registration date April 18, 2018 Last seen - Mar 28, 2018 at 12:40 AM
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 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Apr 3, 2018 at 11:23 AM
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 4 Posts Monday March 26, 2018Registration date April 18, 2018 Last seen - Apr 8, 2018 at 07:55 AM
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
TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Apr 9, 2018 at 12:20 PM
0
Helpful
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 4 Posts Monday March 26, 2018Registration date April 18, 2018 Last seen - Apr 18, 2018 at 01:00 AM
Dear TrowaD

Good morning!!

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

Mohamed Ghouse.
TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Apr 19, 2018 at 10:46 AM
Awesome, thanks for the feedback!
Respond to TrowaD