Let's see if I've got this right:
For each row in the ACM Closing Accounts sheet, if the LSNL column is A1 the Total column is >0 and < 25 you want to:
- copy the Admin: Account Number: (column D) of that sheet to the same headed column (column A) of the ACM Closing Account Accruals sheet
- put the Total value in the same column of the ACM Closing Account Accruals sheet which has the same last 9 digits of the ACM CUSIP column (column H).
Will there ever be more than one column in the ACM Closing Account Accruals with the same last 9 digits?
If the answer to that is No then if you prime your ACM Closing Account Accruals sheet, as you are doing now, with its row 1 data, but add in any column you choose in row 1 the exact text ACM CUSIP, the cusip will be added to that column.
Run this macro instead of yours:
Sub blah()
Dim r As Long, LastRow As Long, r2 As Long
Set DestnSht = Sheets("ACM Closing Account Accruals")
r2 = 2
With DestnSht
Set rngDestnHeaders = Range(.Cells(1), .Cells(1, .Columns.Count).End(xlToLeft))
ReDim ColumnMatch(1 To rngDestnHeaders.Columns.Count) As String
For i = 2 To UBound(ColumnMatch)
If Len(rngDestnHeaders.Cells(i).Value) > 0 Then ColumnMatch(i) = Right(rngDestnHeaders.Cells(i).Value, 9)
Next i
CusipColm = Application.Match("ACM CUSIP", ColumnMatch, 0)
End With
With Sheets("ACM Closing Accounts")
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
r2 = 2
For r = 2 To LastRow
If .Cells(r, 9).Value < 25 And .Cells(r, 5).Value = "A1" And .Cells(r, 9).Value > 0 Then 'Total >0, <25 and LSNL = "A1"
DestnSht.Cells(r2, "A").Value = .Cells(r, "D").Value 'copy admin accnt no.
DestnSht.Cells(r2, CusipColm).Value = .Cells(r, "H").Value 'copy CUSIP
colm = Application.Match(CStr(.Cells(r, 8).Value), ColumnMatch, 0)
If Not IsError(colm) Then
DestnSht.Cells(r2, colm).Value = .Cells(r, "I").Value 'copy total
End If
r2 = r2 + 1
End If
Next r
End With
End Sub
and see if it gives you what you want.