Untitled

Sub ExtractTransactionsToRecon()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim portiaWorkbook 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 portiaFilename As String
    Dim lastRow As Long
    Dim accountRow As Range
    Dim foundRow As Range
    Dim accountCell As Range
    Dim transactionsRow As Long
    
    ' Set folder path and extract folder name
    folderPath = ThisWorkbook.Path & "\"
    folderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") + 1)

    ' Construct the edited output and portia filenames
    editedOutputFilename = folderPath & folderName & ".SS Daily Cash Transactions_Edited_Output.xlsx"
    portiaFilename = folderPath & folderName & ".Portia Settled Cash Balances.xlsx"

    ' Open the Edited Output and Portia workbooks
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    Set portiaWorkbook = Workbooks.Open(portiaFilename, ReadOnly:=True)
    
    ' Set references to sheets
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    Set pasteSheet = editedOutputWorkbook.Sheets("Paste SS Transactions")
    Set portiaTransactionsSheet = portiaWorkbook.Sheets(1) ' Adjust if necessary
    Set transactionsSheet = ThisWorkbook.Sheets("transactions") ' Ensure this sheet exists
    
    ' Clear previous transactions data
    transactionsSheet.Cells.ClearContents
    
    ' Initialize the starting row for transactions output
    transactionsRow = 2 ' Starting from row 2 to leave room for headers
    
    ' Iterate through fund numbers in column A of accounts sheet
    lastRow = accountsSheet.Cells(accountsSheet.Rows.Count, 1).End(xlUp).Row
    For Each accountCell In accountsSheet.Range("A2:A" & lastRow)
        If accountCell.Value <> "" Then
            ' Find matching rows in the Paste SS Transactions sheet
            Set foundRow = pasteSheet.Range("A:A").Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not foundRow Is Nothing Then
                ' Store the first address to avoid looping endlessly
                Dim firstAddress As String
                firstAddress = foundRow.Address
                
                ' Loop through all matches of the fund number
                Do
                    ' Copy the entire row to the transactions sheet
                    foundRow.EntireRow.Copy Destination:=transactionsSheet.Cells(transactionsRow, 1)
                    transactionsRow = transactionsRow + 1
                    
                    ' Find the next occurrence of the fund number
                    Set foundRow = pasteSheet.Range("A:A").FindNext(foundRow)
                Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
            End If
        End If
    Next accountCell
    
    ' Iterate through account numbers in column B of accounts sheet
    For Each accountCell In accountsSheet.Range("B2:B" & lastRow)
        If accountCell.Value <> "" Then
            ' Find matching rows in the Portia transactions sheet
            Set foundRow = portiaTransactionsSheet.Range("A:A").Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not foundRow Is Nothing Then
                ' Store the first address to avoid looping endlessly
                Dim firstAddress As String
                firstAddress = foundRow.Address
                
                ' Loop through all matches of the account number
                Do
                    ' Copy the entire row to the transactions sheet
                    foundRow.EntireRow.Copy Destination:=transactionsSheet.Cells(transactionsRow, 1)
                    transactionsRow = transactionsRow + 1
                    
                    ' Find the next occurrence of the account number
                    Set foundRow = portiaTransactionsSheet.Range("A:A").FindNext(foundRow)
                Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
            End If
        End If
    Next accountCell

    ' Close the external workbooks without saving
    editedOutputWorkbook.Close False
    portiaWorkbook.Close False

    MsgBox "Transaction extraction complete!", vbInformation
End Sub