Can you do this in Excel

[rkebab]

I would like to know if this is possible.

Set up Excel with a main tab, on a worksheet, followed by tab "a", tab "b", and tab "c."

Have it so a line of data on the main tab, identified by an a or b or c in one of the columns, will place the line of data on a list in the proper tab a or b or c.



As new lines of data are added to the main tab, it will continue to update in either tab a or b or c.

Thanks.

[Logit]

Option Explicit

Sub RiteData()
    
    Dim wkSht As Worksheet
    Dim nextRow As Long
    Dim lRow As Long
    Dim i As Integer
    Dim celltxt As String
    
    lRow = Sheets("Main").Cells(Rows.Count, "B").End(xlUp).Row
    Application.ScreenUpdating = False
    For Each wkSht In Sheets
    Sheets("Main").Activate
        For i = 2 To lRow
            celltxt = Sheets("Main").Range("B" & i)
            If Sheets("Main").Range("A" & i).Value = wkSht.Name Then
                Sheets("Main").Activate
                wkSht.Activate
                nextRow = wkSht.Range("A" & Rows.Count).End(xlUp).Row + 1
                Sheets("Main").Range("B" & i).Copy Destination:=wkSht.Range("A" & nextRow)
                Sheets("Main").Range("B" & i) = ""

             End If
        Next i
    
    Next wkSht
    Sheets("Main").Activate
    Sheets("Main").Range("A1").Select
    
    Application.ScreenUpdating = True
End Sub

[rkebab]

Can this be done without code?

[ArviLaanemets]

Originally Posted by rkebab

Can this be done without code?

You can create an ODBC query on sheets a, b, and c, which read data from your main sheet with WHERE clause set. You can set those queries to be refreshed when workbook is opened, and user can refresh queries manually at any time too.

But the question remains, for what do you need this at all? I can't see any valid reason for this!

[p45cal]

In the attached called msofficeforums50445 there are single-celled formulae in cells A1 of sheets a,b,& c.

In the attached file called msofficeforums50445pq theres a power query solution on each of those sheets, they need refreshing (like a pivot table) by clicking on Refresh All in the Queries & Connections section of the Data tab of the ribbon.

[kilroyscarnival]

Originally Posted by rkebab

I would like to know if this is possible.

Set up Excel with a main tab, on a worksheet, followed by tab "a", tab "b", and tab "c."

Have it so a line of data on the main tab, identified by an a or b or c in one of the columns, will place the line of data on a list in the proper tab a or b or c.

As new lines of data are added to the main tab, it will continue to update in either tab a or b or c.

Thanks.

If you have the FILTER function, that would be one way of getting a filtered list meeting whatever criteria makes it A, B, or C material.

This video helped me: https://www.youtube.com/watch?v=Onudkw9DMlU&t=52s