Apr 09 2022 04:15 PM
Apr 09 2022 04:15 PM
My company is using an Excel workbook to track 3 types of transactions (Cash, Credit, Loan). On column A of the MAIN sheet, one of these values is selected and then the remaining information is entered for that particular transaction (date, description, amount etc).
What I am trying to do is create 3 other worksheets (cash, credit, loan). The cash sheet would show only the rows with CASH in column A of the MAIN sheet, the credit sheet would show rows with CREDIT in column A, and loan would show only the rows with LOAN in column A. I've tried advanced filtering, but the issue is, I need the updates to happen in real time. The sheet is updated daily. So as they enter a new row of data, I'd like that row to also appear on the sheet it belongs to.
Appreciate any help.
Apr 09 2022 07:00 PM
I would prefer VBA to achieve this. You may use the attached file as a Template which is a Macro-Enabled File with .xlsm file extension.
Or you may place the following code on ThisWorkbook Module in your file and to do so, follow these steps...
1. Open your file and press Alt+F11 to open VB Editor.
2. On VB Editor, click on ThisWorkbook in the Project Explorer of VBA Project of your file and paste the
code given below into the opened Code Window of ThisWorkbook Module.
3. Save your file as a Macro-Enabled File in the end.
Code for ThisWorkbook Module:
Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim shName As String shName = LCase(Sh.Name) If shName <> "cash" And shName <> "credit" And shName <> "loan" Then Exit Sub Dim wsData As Worksheet Dim RngSource As Range Application.ScreenUpdating = False Set wsData = ThisWorkbook.Worksheets("Main") Set RngSource = wsData.Range("A1").CurrentRegion Sh.Range("XFD1").Value = wsData.Range("A1").Value Sh.Range("XFD2").Value = Sh.Name Sh.Range("A1").CurrentRegion.Clear RngSource.AdvancedFilter xlFilterCopy, Sh.Range("XFD1:XFD2"), Sh.Range("A1") Application.ScreenUpdating = True End Sub
Note: To make this work, the Sheet Names should match with the transaction types available in column A of the Main Sheet like CASH, CREDIT and LOAN. The name of the data sheet should be Main and if the name of the actual data sheet is different, you can tweak it in line#12 of the code.
Apr 09 2022 07:06 PMSolution