Untitled

' 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
            ' 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, 0).Value ' Column A
                            .Cells(transactionsRow, 2).Value = "Reams" ' Set "Reams" as Source for Column B
                            .Cells(transactionsRow, 3).Value = foundRow.Offset(0, 2).Value ' Date (Column C from Portia)
                            .Cells(transactionsRow, 4).Value = foundRow.Offset(0, 3).Value ' CUSIP (Column D from Portia)
                            .Cells(transactionsRow, 5).Value = updatedGValue ' Description (Column G from Portia)
                            .Cells(transactionsRow, 6).Value = foundRow.Offset(0, 21).Value ' Amount (Column V from Portia)
                            .Cells(transactionsRow, 7).Value = foundRow.Offset(0, 31).Value ' Principal (Column AF from Portia)
                            .Cells(transactionsRow, 8).Value = foundRow.Offset(0, 32).Value ' Interest (Column AG from Portia)
                        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.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