Previous Topic Next Topic

Auto populate from master file to other

Solved/Closed
MohamedGhouse Posts 4 Registration date Monday 26 March 2018 Status Member Last seen 18 April 2018 - 26 Mar 2018 à 06:06
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 19 Apr 2018 à 10:46
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.
Related:

2 responses

Réponse 1 / 2
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
27 Mar 2018 à 12:08
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 26 March 2018 Status Member Last seen 18 April 2018
28 Mar 2018 à 00:40
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 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
3 Apr 2018 à 11:23
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 26 March 2018 Status Member Last seen 18 April 2018
8 Apr 2018 à 07:55
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
Réponse 2 / 2
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
9 Apr 2018 à 12:20
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 26 March 2018 Status Member Last seen 18 April 2018
18 Apr 2018 à 01:00
Dear TrowaD

Good morning!!

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

Mohamed Ghouse.
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
19 Apr 2018 à 10:46
Awesome, thanks for the feedback!