Untitled

Sub ExtractTransactions()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim portiaTransactionsWorkbook As Workbook
    Dim accountsSheet As Worksheet
    Dim transactionsSheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim portiaTransactionsSheet As Worksheet
    Dim folderPath As String
    Dim folderName As String
    Dim editedOutputFilename As String
    Dim portiaTransactionsFilename As String
    Dim lastRow As Long
    Dim accountRow As Range
    Dim foundRow As Range
    Dim accountNumber As String
    Dim fundNumber As String
    Dim transactionsRow As Long
    Dim firstAddress As String

    ' 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 & ".Portia Transactions.xlsx" ' Change the filename as necessary

    ' Open the Edited Output workbook
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    Set transactionsSheet = ThisWorkbook.Sheets("transactions")

    ' Clear previous data in transactions sheet
    transactionsSheet.Cells.ClearContents

    ' Start pasting rows from row 2
    transactionsRow = 2

    ' Loop through each fund number in Column A of accounts sheet
    lastRow = accountsSheet.Cells(accountsSheet.Rows.Count, 1).End(xlUp).Row
    For Each accountRow In accountsSheet.Range("A2:A" & lastRow)
        fundNumber = accountRow.Value
        
        ' Find the fund number in the Paste SS Transactions sheet
        Set pasteSheet = editedOutputWorkbook.Sheets("SS holdings-paste from SS file ")
        lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
        Set foundRow = pasteSheet.Range("A1:A" & lastRow).Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not foundRow Is Nothing Then
            firstAddress = foundRow.Address ' Save the first address to avoid endless loops
            Do
                ' Copy the entire row to the transactions sheet
                foundRow.EntireRow.Copy Destination:=transactionsSheet.Cells(transactionsRow, 1)
                transactionsRow = transactionsRow + 1
                
                ' Continue searching for the next occurrence of the FundNumber
                Set foundRow = pasteSheet.Range("A1:A" & lastRow).FindNext(foundRow)
            Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
        End If
    Next accountRow

    ' Now loop through each account number in Column B of accounts sheet
    For Each accountRow In accountsSheet.Range("B2:B" & lastRow)
        accountNumber = accountRow.Value
        
        ' Open the Portia transactions workbook
        Set portiaTransactionsWorkbook = Workbooks.Open(portiaTransactionsFilename)
        Set portiaTransactionsSheet = portiaTransactionsWorkbook.Sheets(1) ' Adjust the sheet index if necessary

        ' Find the account number in the Portia transactions sheet
        lastRow = portiaTransactionsSheet.Cells(portiaTransactionsSheet.Rows.Count, 1).End(xlUp).Row
        Set foundRow = portiaTransactionsSheet.Range("A1:A" & lastRow).Find(What:=accountNumber, LookIn:=xlValues, LookAt:=xlWhole)

        If Not foundRow Is Nothing Then
            firstAddress = foundRow.Address ' Save the first address to avoid endless loops
            Do
                ' Copy the entire row to the transactions sheet
                foundRow.EntireRow.Copy Destination:=transactionsSheet.Cells(transactionsRow, 1)
                transactionsRow = transactionsRow + 1
                
                ' Continue searching for the next occurrence of the AccountNumber
                Set foundRow = portiaTransactionsSheet.Range("A1:A" & lastRow).FindNext(foundRow)
            Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
        End If

        portiaTransactionsWorkbook.Close False ' Close the Portia transactions workbook
    Next accountRow

    ' Save the edited output workbook
    editedOutputWorkbook.Close False
    
    MsgBox "Transaction extraction complete!", vbInformation
End Sub