Copy Data to Other Sheets Using a Function/Criteria

[TheJax]

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.

 

[dmt32]

Hi,

I would have thought Advanced Filter should be able to do what you want

Assuming that your worksheets are named Cash, Credit & Loan and all have the SAME Column headings in ROW 1 as the MAIN worksheet which has the values in Column A , Cash, Credit or Loan then give following a try & see if helps you

Place following code in a STANDARD module

Sub FilterTransActions(ByVal sh As Object)
    Dim FilterCriteria  As Range, DataRange As Range
    Dim ws              As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Main")
    'get data range (sheet must not be protected)
    Set DataRange = ws.Range("A1").CurrentRegion
    
    'criteria range
    Set FilterCriteria = ws.Cells(1, ws.Columns.Count).Resize(2, 1)
    'criteria heading
    FilterCriteria(1) = ws.Range("A1").Value
    'criteria value
    FilterCriteria(2).Value = sh.Name
   
    If ws.FilterMode Then ws.ShowAllData

    'copy filtered data to sheet
     ws.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=FilterCriteria, _
                                         CopyToRange:=sh.Range(DataRange.Rows(1).Address)
End Sub

Place following code in each of the Cash, Credit & Loan worksheets CODE PAGE

Private Sub Worksheet_Activate()
    FilterTransActions Me
End Sub

Hopefully, when you select (Activate) each of these sheets, Filter code should update the sheet with its data from main sheet.

Dave

 

[TheJax]

Hi,

I would have thought Advanced Filter should be able to do what you want

Assuming that your worksheets are named Cash, Credit & Loan and all have the SAME Column headings in ROW 1 as the MAIN worksheet which has the values in Column A , Cash, Credit or Loan then give following a try & see if helps you

Place following code in a STANDARD module

Sub FilterTransActions(ByVal sh As Object)
    Dim FilterCriteria  As Range, DataRange As Range
    Dim ws              As Worksheet
  
    Set ws = ThisWorkbook.Worksheets("Main")
    'get data range (sheet must not be protected)
    Set DataRange = ws.Range("A1").CurrentRegion
  
    'criteria range
    Set FilterCriteria = ws.Cells(1, ws.Columns.Count).Resize(2, 1)
    'criteria heading
    FilterCriteria(1) = ws.Range("A1").Value
    'criteria value
    FilterCriteria(2).Value = sh.Name
 
    If ws.FilterMode Then ws.ShowAllData

    'copy filtered data to sheet
     ws.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=FilterCriteria, _
                                         CopyToRange:=sh.Range(DataRange.Rows(1).Address)
End Sub

Place following code in each of the Cash, Credit & Loan worksheets CODE PAGE

Private Sub Worksheet_Activate()
    FilterTransActions Me
End Sub

Hopefully, when you select (Activate) each of these sheets, Filter code should update the sheet with its data from main sheet.

Dave

Hi,

I would have thought Advanced Filter should be able to do what you want

Assuming that your worksheets are named Cash, Credit & Loan and all have the SAME Column headings in ROW 1 as the MAIN worksheet which has the values in Column A , Cash, Credit or Loan then give following a try & see if helps you

Place following code in a STANDARD module

Sub FilterTransActions(ByVal sh As Object)
    Dim FilterCriteria  As Range, DataRange As Range
    Dim ws              As Worksheet
   
    Set ws = ThisWorkbook.Worksheets("Main")
    'get data range (sheet must not be protected)
    Set DataRange = ws.Range("A1").CurrentRegion
   
    'criteria range
    Set FilterCriteria = ws.Cells(1, ws.Columns.Count).Resize(2, 1)
    'criteria heading
    FilterCriteria(1) = ws.Range("A1").Value
    'criteria value
    FilterCriteria(2).Value = sh.Name
  
    If ws.FilterMode Then ws.ShowAllData

    'copy filtered data to sheet
     ws.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=FilterCriteria, _
                                         CopyToRange:=sh.Range(DataRange.Rows(1).Address)
End Sub

Place following code in each of the Cash, Credit & Loan worksheets CODE PAGE

Private Sub Worksheet_Activate()
    FilterTransActions Me
End Sub

Hopefully, when you select (Activate) each of these sheets, Filter code should update the sheet with its data from main sheet.

Dave

Thanks!