#1
|
|||
|
|||
Can you do this in Excel
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Can this be done without code?
|
#4
|
|||
|
|||
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! |
#5
|
||||
|
||||
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. |
#6
|
|||
|
|||
Quote:
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 |