Copy to other sheets using formula and criteria

[THECAESAERC]

Hello,

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.

Thanks.

[#2]

@THECAESAERC 

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.

[#3 · Accepted solution]

Just forgot to mention that on Main (Data Sheet) in the attached file, you may replace the column headers with the actual column headers and add or remove column headers as per your requirement but the column headers must be in row#1 and data should start from row#2 with no blank rows in between.

[#4]

Thank you! Worked perfect!