Looking to pull rows from one master sheet to another that will update both when information entered into the master

[Vertigobubba]

Hello again,

Thank you so much for your help, I am learning so much here today.

Here is what I am hoping to figure out how to to.

Column A has a dropdown in it with a series of Text values.

At the bottom of the sheet I have tabs for each of those named text values.

I would like to populate the entire data row from each of text value names into their applicable tab. However, I need it to be linked, so that when the Master List Tab is updated, the change will automatically populate it respective tab.

For example, as per the attached image.

I want to pull all of the Rows with a Region Name of ALTLANTIC and populate them into the Atlantic tab, and when I update something in the Master List in an ATLANTIC row, it will update in the Atlantic tab as well.

I will be doing this for all the regions of course, but any help would be greatly appreciated. I know how to pull individual cell information and link it to another tab, but have never tried to pull entire rows.

Thank you!!!!

 

[vcoolio]

Hello Vertigobubba,

This may help:

Sub VertigoTest()

              Dim i As Long, lr As Long
              Dim sh As Worksheet, wsD As Worksheet, ar As Variant
        
Application.ScreenUpdating = False
        
              Set sh = Sheets("Master List")
              lr = sh.Range("A" & Rows.Count).End(xlUp).Row
              sh.Range("A1:A" & lr).AdvancedFilter 2, , sh.[Z1], 1  'Unique values moved temporarily to Column Z.
              sh.Range("Z2", sh.Range("Z" & sh.Rows.Count).End(xlUp)).Sort [Z2], 1 'Unique values sorted.
              ar = sh.Range("Z2", sh.Range("Z" & sh.Rows.Count).End(xlUp))
              
       For i = 1 To UBound(ar)
                    
              Set wsD = Sheets(CStr(ar(i, 1))) '---->Destination worksheets.
              wsD.UsedRange.Clear
                    
              With sh.[A1].CurrentRegion
                   .AutoFilter 1, ar(i, 1)
                   .Copy wsD.[A1]
                   .AutoFilter
             End With
                   wsD.Columns.AutoFit
       Next i

             sh.Columns("Z").Clear 'Clear helper column to be used again as needed.

Application.Goto sh.[A1]
Application.ScreenUpdating = True

End Sub

I'm assuming that Column Z is totally empty to be used as a helper column. If not, pick a column elsewhere on the master sheet that is and change the code references to it.
Assign the code to a button. Each time that you enter/update any data and click on the button, all destination sheets will be refreshed.
You will need to ensure that all the names in Column A exactly match the sheet tab names.

Test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[Vertigobubba]

Hello Vertigobubba,

This may help:

Sub VertigoTest()

              Dim i As Long, lr As Long
              Dim sh As Worksheet, wsD As Worksheet, ar As Variant
       
Application.ScreenUpdating = False
       
              Set sh = Sheets("Master List")
              lr = sh.Range("A" & Rows.Count).End(xlUp).Row
              sh.Range("A1:A" & lr).AdvancedFilter 2, , sh.[Z1], 1  'Unique values moved temporarily to Column Z.
              sh.Range("Z2", sh.Range("Z" & sh.Rows.Count).End(xlUp)).Sort [Z2], 1 'Unique values sorted.
              ar = sh.Range("Z2", sh.Range("Z" & sh.Rows.Count).End(xlUp))
             
       For i = 1 To UBound(ar)
                   
              Set wsD = Sheets(CStr(ar(i, 1))) '---->Destination worksheets.
              wsD.UsedRange.Clear
                   
              With sh.[A1].CurrentRegion
                   .AutoFilter 1, ar(i, 1)
                   .Copy wsD.[A1]
                   .AutoFilter
             End With
                   wsD.Columns.AutoFit
       Next i

             sh.Columns("Z").Clear 'Clear helper column to be used again as needed.

Application.Goto sh.[A1]
Application.ScreenUpdating = True

End Sub

I'm assuming that Column Z is totally empty to be used as a helper column. If not, pick a column elsewhere on the master sheet that is and change the code references to it.
Assign the code to a button. Each time that you enter/update any data and click on the button, all destination sheets will be refreshed.
You will need to ensure that all the names in Column A exactly match the sheet tab names.

Test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

Thank you so much vcoolio, I have never used VBA before so while I understand the desired actions, I've never written the formulas for one and am at a total loss.

 

[Fluff]

How about with a formula

=FILTER('Master list'!A2:Z100,'Master list'!A2:A100="atlantic","No data")

 

[Vertigobubba]

How about with a formula

=FILTER('Master list'!A2:Z100,'Master list'!A2:A100="atlantic","No data")

That works, and thank you so much, but the formula won't let me filter sort data on the individual tab sheet. I don't want to change the date on the tab sheets, but would need to manipulate it to sort by date/score etc?

 

[Fluff]

In that case you will need to use VBA.

 

[Vertigobubba]

In that case you will need to use VBA.

Thank you again Fluff, you are amazing! I will try and figure that out. Cheers!