Previous Topic Next Topic

Auto populate from master file to other

Solved/Closed
MohamedGhouse Posts 4 Registration date Monday March 26, 2018 Status Member Last seen April 18, 2018 - Updated on Apr 22, 2018 at 12:10 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - 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.
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 27, 2018 at 12:08 PM
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
0
MohamedGhouse Posts 4 Registration date Monday March 26, 2018 Status Member Last seen April 18, 2018
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
0
MohamedGhouse Posts 4 Registration date Monday March 26, 2018 Status Member Last seen April 18, 2018
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
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 9, 2018 at 12:20 PM
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
0
MohamedGhouse Posts 4 Registration date Monday March 26, 2018 Status Member Last seen April 18, 2018
Apr 18, 2018 at 01:00 AM
Dear TrowaD

Good morning!!

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

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

Similar discussions

Opening a corrupted Illustrator .ai file
BenjaminOwens -
williammcdowell -

1 response
Uh-Oh, we can't recover your account online
Karen -
HelpiOS -

3 responses