This little program reads some date from one row on a sheet in a
workbook, then goes to another wb and inserts a sheet and pastes the
data then goes back to the first wb/sheet and loops till it's out of
data. I know all the data could be read at once and then all the
pasting done with a dynamic array but I haven't been able to make that
work. "DataTable.xls" will be a new file each day hence the need for a
dynamic array to read the data. Any help would be greatly appreciated.


Public Sub RunReports()

Dim vManagers As Range, vManager As Range
Dim DataArray(5)

Windows("DataTable.xls").Activate

On Error Resume Next
Set vManagers = Range("D:D")
On Error GoTo 0
If Not vManagers Is Nothing Then
For Each vManager In vManagers

If vManager.Value = "S. O' Neil" Then

vManager.Activate
DataArray(1) = ActiveCell.Offset(0, -3) 'Date
DataArray(2) = ActiveCell.Offset(0, -1) 'Borrower
Name
DataArray(3) = ActiveCell.Offset(0, 1) 'Retail/
Broker
DataArray(4) = ActiveCell.Offset(0, 3) 'Loan
Number
DataArray(5) = ActiveCell.Offset(0, 7) 'Amount
Windows("Check Deposit Report.xls").Activate
Sheets("CHECK-DEPOSIT").Copy After:=Sheets(1)
ActiveSheet.Unprotect
Range("F43").Value = DataArray(1)
Range("B43").Value = DataArray(2)
If DataArray(3) = "Broker" Then
Range("F32").Value = DataArray(3)
Else
Range("F32").Value = "Retail"
End If
Range("A43").Value = DataArray(4)
Range("I27").Value = -DataArray(5)
Windows("DataTable.xls").Activate
End If
Next
End If

Windows("Check Deposit Report.xls").Activate

End Sub