Untitled

Sub ExtractTransactions()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim portiaWorkbook As Workbook
    Dim accountsSheet As Worksheet
    Dim transactionsSheet As Worksheet
    Dim pasteSSTransactionsSheet As Worksheet
    Dim portiaTransactionsSheet As Worksheet
    Dim folderPath As String
    Dim folderName As String
    Dim editedOutputFilename As String
    Dim portiaTransactionsFilename As String
    Dim fundNumber As String
    Dim accountNumber As String
    Dim foundRow As Range
    Dim lastRow As Long
    Dim currentRow As Long
    Dim ws As Worksheet
    
    ' Set folder path and extract folder name
    folderPath = ThisWorkbook.Path & "\"
    folderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") + 1)

    ' Construct the edited output filename
    editedOutputFilename = folderPath & folderName & ".SS Daily Cash Transactions_Edited_Output.xlsx"
    
    ' Construct the Portia transactions filename
    portiaTransactionsFilename = folderPath & folderName & ".Manual Cash Trans.xlsx"

    ' Open the Edited Output workbook
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    
    ' Set the accounts and transactions sheets
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    Set transactionsSheet = ThisWorkbook.Sheets("transactions")
    Set pasteSSTransactionsSheet = editedOutputWorkbook.Sheets("Paste SS Transactions")
    
    ' Clear existing data in the transactions sheet
    transactionsSheet.Cells.ClearContents
    transactionsSheet.Range("A1").Value = "Fund Number"
    transactionsSheet.Range("B1").Value = "Account Number"
    transactionsSheet.Range("C1").Value = "Transaction Details"

    ' Start from the second row for output
    currentRow = 2

    ' Loop through each account in the accounts sheet
    lastRow = accountsSheet.Cells(accountsSheet.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        fundNumber = accountsSheet.Cells(i, 1).Value ' Column A
        accountNumber = accountsSheet.Cells(i, 2).Value ' Column B
        
        ' Find matching fund number in the Paste SS Transactions sheet
        Set foundRow = pasteSSTransactionsSheet.Columns("A").Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not foundRow Is Nothing Then
            ' Loop through all matches of the fund number
            Do
                ' Copy the entire row of the transaction details to the transactions sheet
                transactionsSheet.Cells(currentRow, 1).Value = fundNumber
                transactionsSheet.Cells(currentRow, 2).Value = accountNumber
                transactionsSheet.Cells(currentRow, 3).Value = foundRow.EntireRow.Value
                
                currentRow = currentRow + 1
                Set foundRow = pasteSSTransactionsSheet.Columns("A").FindNext(foundRow)
            Loop While Not foundRow Is Nothing And foundRow.Address <> foundRow.Address
        End If
        
        ' Find matching account number in the Portia transactions sheet
        On Error Resume Next
        Set portiaWorkbook = Workbooks.Open(portiaTransactionsFilename, ReadOnly:=True)
        On Error GoTo 0
        
        If Not portiaWorkbook Is Nothing Then
            Set portiaTransactionsSheet = portiaWorkbook.Sheets(1) ' Adjust the sheet index as necessary
            
            Set foundRow = portiaTransactionsSheet.Columns("A").Find(What:=accountNumber, LookIn:=xlValues, LookAt:=xlWhole)
            
            If Not foundRow Is Nothing Then
                ' Loop through all matches of the account number
                Do
                    ' Copy the entire row of the transaction details to the transactions sheet
                    transactionsSheet.Cells(currentRow, 1).Value = fundNumber
                    transactionsSheet.Cells(currentRow, 2).Value = accountNumber
                    transactionsSheet.Cells(currentRow, 3).Value = foundRow.EntireRow.Value
                    
                    currentRow = currentRow + 1
                    Set foundRow = portiaTransactionsSheet.Columns("A").FindNext(foundRow)
                Loop While Not foundRow Is Nothing And foundRow.Address <> foundRow.Address
            End If
            
            portiaWorkbook.Close False
        Else
            MsgBox "Failed to open the Portia workbook!", vbCritical
        End If
    Next i

    ' Save and close the edited output workbook
    editedOutputWorkbook.Close False

    MsgBox "Transactions extraction completed!", vbInformation
End Sub