Can you do this in Excel

rkebab · #1 02-17-2023, 09:18 PM

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 · #2 02-18-2023, 01:44 PM

Code:

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 · #3 02-18-2023, 09:22 PM

Can this be done without code?

ArviLaanemets · #4 02-18-2023, 11:51 PM

Quote:

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 · #5 02-19-2023, 05:32 AM

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 · #6 02-20-2023, 06:52 AM

Quote:

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel gives Normal.dotm: File format is not valid message (yes, Excel, not Word)	Windspeed	Excel	10	08-03-2022 04:29 PM
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data?	Hoser	Word	1	03-17-2017 03:47 PM
How To Open an Macro From 2003 Excel in 2013 Excel Spread Sheet?	ADubin	Excel Programming	3	02-08-2015 04:57 AM
[Excel 2007] Building Power Point Slides from data in an Excel Table	bremen22	Excel Programming	1	08-07-2013 11:01 AM
Excel 2011 can't open old Excel 98 or Excel X files	FLJohnson	Excel	8	05-09-2012 11:26 PM