Auto populate appropriate sheets with data entered into master sheet

[LambChoptheKid]

Hello, I am building a Master Contact List workbook with various sheets.

The first sheet is a master list that contains all contact info for everyone that we have access to.
The following sheets are company-specific and contain only those contacts which pertain to that specific company.
Every sheet is contained within a single workbook.

My goal is to set up a system where an update to the master sheet will automatically plug that new contact into the appropriate sheet depending on what company I label them as being a part of.

I imagine this can be done by using VBA IF statements, but I'm struggling to find a solution.

Whoever has any ideas about what can be done, or if this is possible at all, I would appreciate your input.

Thank you

 

[awoohaw]

Can you provide some sample (anonymized) data from the master, and some sample of the information types from thee master sheetthat you need on other sheets? The xl2bb add in is great for that.

 

[vcoolio]

Hello LambChoptheKid,

Check the link to a sample file here.
This could be a starting point for you so hopefully you can elaborate further as to what you were hoping to achieve.

The code implemented in the sample is as follows:-

Option Explicit

Sub Test()

              Dim i As Long, lr As Long
              Dim sh As Worksheet, ws As Worksheet, ar As Variant
        
Application.ScreenUpdating = False
        
              Set sh = Sheets("Input")
              lr = sh.Range("A" & Rows.Count).End(xlUp).Row
              sh.Range("B1:B" & lr).AdvancedFilter 2, , sh.[M1], 1
              sh.Range("M2", sh.Range("M" & sh.Rows.Count).End(xlUp)).Sort [M2], 1
              ar = sh.Range("M2", sh.Range("M" & sh.Rows.Count).End(xlUp))
              
       For i = 1 To UBound(ar)
              If Not Evaluate("ISREF('" & CStr(ar(i, 1)) & "'!A1)") Then
                    Sheets.Add(after:=Sheets(Worksheets.Count)).Name = ar(i, 1)
              End If
                    
              Set ws = Sheets(CStr(ar(i, 1)))
              ws.UsedRange.Clear
                    
              With sh.[A1].CurrentRegion
                   .AutoFilter 2, ar(i, 1)
                   .Copy ws.[A1]
                   .AutoFilter
             End With
                   ws.Columns.AutoFit
       Next i

             sh.Columns("M").Clear
             Application.Goto sh.[A1]

Application.ScreenUpdating = True

End Sub

Basically, it checks for company names in Column B, creates new worksheets for each individual company then adds the relevant data for each company to the individual company sheet(without duplication).
Click on the "TEST" button to see how it works. When new data is added for any company, simply click on the button to 'refresh' the destination sheets.

Let us know what you think.

Cheerio,
vcoolio.

 

[LambChoptheKid]

Can you provide some sample (anonymized) data from the master, and some sample of the information types from thee master sheetthat you need on other sheets? The xl2bb add in is great for that.

Unfortunately I'm not authorized to use that specific addon at my work, I could provide some screenshots if that would be helpful.

 

[LambChoptheKid]

Hello LambChoptheKid,

Check the link to a sample file here.
This could be a starting point for you so hopefully you can elaborate further as to what you were hoping to achieve.

The code implemented in the sample is as follows:-

Option Explicit

Sub Test()

              Dim i As Long, lr As Long
              Dim sh As Worksheet, ws As Worksheet, ar As Variant
       
Application.ScreenUpdating = False
       
              Set sh = Sheets("Input")
              lr = sh.Range("A" & Rows.Count).End(xlUp).Row
              sh.Range("B1:B" & lr).AdvancedFilter 2, , sh.[M1], 1
              sh.Range("M2", sh.Range("M" & sh.Rows.Count).End(xlUp)).Sort [M2], 1
              ar = sh.Range("M2", sh.Range("M" & sh.Rows.Count).End(xlUp))
             
       For i = 1 To UBound(ar)
              If Not Evaluate("ISREF('" & CStr(ar(i, 1)) & "'!A1)") Then
                    Sheets.Add(after:=Sheets(Worksheets.Count)).Name = ar(i, 1)
              End If
                   
              Set ws = Sheets(CStr(ar(i, 1)))
              ws.UsedRange.Clear
                   
              With sh.[A1].CurrentRegion
                   .AutoFilter 2, ar(i, 1)
                   .Copy ws.[A1]
                   .AutoFilter
             End With
                   ws.Columns.AutoFit
       Next i

             sh.Columns("M").Clear
             Application.Goto sh.[A1]

Application.ScreenUpdating = True

End Sub

Basically, it checks for company names in Column B, creates new worksheets for each individual company then adds the relevant data for each company to the individual company sheet(without duplication).
Click on the "TEST" button to see how it works. When new data is added for any company, simply click on the button to 'refresh' the destination sheets.

Let us know what you think.

Cheerio,
vcoolio.

Thank you for the help! I'm not super familiar with VBA so this bit of code is helpful for me to experiment with. Though unfortunately I haven't been able to get the sample file you provided to work.

Appreciate the help!

 

[vcoolio]

Hello LambChoptheKid,,

That link may have expired but you can upload a sample of your workbook to a file sharing site such as Drop Box or WeTransfer( as I used). We can then test methods to attempt to help you.
Make sure that the sample is an exact replica of your actual workbook and if your data is sensitive, then please use dummy data.

Cheerio,
vcoolio.