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
    Dim valueW As Variant
    Dim valueV As Variant
    Dim cashCUSIP As String
    Dim updatedGValue As String

    ' Set folder path and extract folder name
    folderPath = ThisWorkbook.Path & "\"
    folderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") + 1)

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

    ' Open the Edited Output workbook and Portia workbook only once
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    Set transactionsSheet = ThisWorkbook.Sheets("transactions")
    Set portiaTransactionsWorkbook = Workbooks.Open(portiaTransactionsFilename)
    Set portiaTransactionsSheet = portiaTransactionsWorkbook.Sheets(1) ' Adjust the sheet index if necessary

    ' Clear previous data and formatting in transactions sheet
    With transactionsSheet
        .Cells.ClearContents
        .Cells.ClearFormats
    End With

    ' Add column headers in row 1 from A:H
    transactionsSheet.Range("A1:H1").Value = Array("Account", "Source", "Date", "CUSIP", "Description", "Amount", "Principal", "Interest")

    ' Start pasting rows from row 2
    transactionsRow = 2

    ' Set Paste SS Transactions sheet
    Set pasteSheet = editedOutputWorkbook.Sheets("Paste SS Transactions")

    ' Loop through each fund number in Column A of the accounts sheet
    lastRow = accountsSheet.Cells(accountsSheet.Rows.Count, 1).End(xlUp).Row
    For Each accountRow In accountsSheet.Range("A2:A" & lastRow)
        fundNumber = accountRow.Value
        cashCUSIP = accountRow.Offset(0, 2).Value ' Get the CUSIP from Column C

        ' Find the fund number in the Paste SS Transactions sheet
        Set foundRow = pasteSheet.Columns("A").Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not foundRow Is Nothing Then
            firstAddress = foundRow.Address
            Do
                ' Check for additional criteria in columns F and G
                If Right(foundRow.Offset(0, 5).Value, 4) = "/000" And foundRow.Offset(0, 6).Value = "US DOLLAR" Then
                    ' Get values for W and V
                    valueW = foundRow.Offset(0, 22).Value ' Column W
                    valueV = foundRow.Offset(0, 21).Value ' Column V
                    
                    ' Check if either valueW or valueV exists
                    If Not IsEmpty(valueW) Or Not IsEmpty(valueV) Then
                        ' Check if CUSIP matches, if so, exclude
                        If foundRow.Offset(0, 14).Value <> cashCUSIP Then ' Column O
                            ' Copy values into the transactions sheet
                            transactionsSheet.Cells(transactionsRow, 1).Value = fundNumber  ' Column A - Account
                            transactionsSheet.Cells(transactionsRow, 2).Value = "BK"       ' Column B - Source
                            transactionsSheet.Cells(transactionsRow, 3).Value = foundRow.Offset(0, 12).Value ' Column M - Date
                            transactionsSheet.Cells(transactionsRow, 4).Value = foundRow.Offset(0, 13).Value ' Column N - CUSIP
                            transactionsSheet.Cells(transactionsRow, 5).Value = ""         ' Blank column after Date for BK
                            
                            ' Check for values in Column W and adjust accordingly
                            If Not IsEmpty(valueW) Then
                                transactionsSheet.Cells(transactionsRow, 6).Value = -valueW ' Column W multiplied by -1 (Amount)
                            Else
                                transactionsSheet.Cells(transactionsRow, 6).Value = valueV  ' Column V (Amount)
                            End If
                            
                            ' Copy values from AF and AG for Principal and Interest
                            transactionsSheet.Cells(transactionsRow, 7).Value = foundRow.Offset(0, 31).Value ' Column AF - Principal
                            transactionsSheet.Cells(transactionsRow, 8).Value = foundRow.Offset(0, 32).Value ' Column AG - Interest
                            
                            transactionsRow = transactionsRow + 1 ' Move to the next row in transactions sheet
                        End If
                    End If
                End If
                
                ' Continue searching for the next occurrence of the FundNumber
                Set foundRow = pasteSheet.Columns("A").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 the accounts sheet
    For Each accountRow In accountsSheet.Range("B2:B" & lastRow)
        accountNumber = accountRow.Value

        ' Find the account number in the Portia transactions sheet
        Set foundRow = portiaTransactionsSheet.Columns("A").Find(What:=accountNumber, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not foundRow Is Nothing Then
            firstAddress = foundRow.Address
            Do
                ' Check if Column F is "-CASH-" and that there is a value in W or V before pasting
                If foundRow.Offset(0, 5).Value = "-CASH-" Then ' Column F
                    valueW = foundRow.Offset(0, 22).Value ' Column W
                    valueV = foundRow.Offset(0, 21).Value ' Column V
                    
                    If Not IsEmpty(valueW) Or Not IsEmpty(valueV) Then
                        ' Update value of G by adding an apostrophe
                        updatedGValue = "'" & foundRow.Offset(0, 6).Value ' Column G with an apostrophe
                        
                        ' Check if the updated value of G matches the cash CUSIP, if so, exclude
                        If updatedGValue <> cashCUSIP Then
                            ' Paste values into the transactions sheet without formatting
                            With transactionsSheet
                                .Cells(transactionsRow, 1).Value = foundRow.Offset(0, 2).Value ' Column C - Account
                                .Cells(transactionsRow, 2).Value = foundRow.Offset(0, 3).Value ' Column D - Source
                                .Cells(transactionsRow, 3).Value = updatedGValue              ' Column G with apostrophe - Date
                                .Cells(transactionsRow, 4).Value = foundRow.Offset(0, 21).Value ' Column V - CUSIP
                            End With
                            transactionsRow = transactionsRow + 1 ' Move to the next row in transactions sheet
                        End If
                    End If
                End If
                
                ' Continue searching for the next occurrence of the AccountNumber
                Set foundRow = portiaTransactionsSheet.Columns("A").FindNext(foundRow)
            Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
        End If
    Next accountRow

    ' Close the workbooks after processing
    editedOutputWorkbook.Close False
    portiaTransactionsWorkbook.Close False

    MsgBox "Transaction extraction complete!", vbInformation
End Sub