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

I would just loop through the range and avoid the array altogether.

Option Explicit
Public Sub RunReports()

Dim vManagers As Range
Dim vManager As Range
Dim TempWks As Worksheet

With Workbooks("datatable.xls").Worksheets("Somesheetna mehere")
'headers in row 1????
Set vManagers = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
End With

For Each vManager In vManagers.Cells
With Workbooks("check deposit report.xls")
.Worksheets("Check-Deposit").Copy _
befo=.Sheets(1)
End With
Set TempWks = ActiveSheet

With TempWks
.Unprotect
.Range("F43").Value = vManager.Offset(0, -3).Value 'date
.Range("b43").Value = vManager.Offset(0, -1).Value 'borrower
If LCase(vManager.Offset(0, 1).Value) = "broker" Then
.Range("f32").Value = "Broker"
Else
.Range("F32").Value = "Retail"
End If
.Range("a43").Value = vManager.Offset(0, 3).Value 'Loan Number
.Range("i27").Value = vManager.Offset(0, 7).Value 'Amount

'protect??
.Protect
End With
Next vManager
End Sub

You wanted a different worksheet for each cell in column D, right?

John Pierce wrote:

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

--

Dave Peterson

I'm back. That was weird. Therre was this intense light and then . , ,
nothing, till three weeks later.
So where was I?
Dave, I tried your code. It ran and ran and ran, hundreds of pages
when there should have been only about 70 or so. Also, it picked up
all the managers instead of just S. O'Neil.
I would really like to use a dynamic array to do this because I would
like to learn to work with them. Could this program be converted to
that?

It creates a new sheet for every cell in that range. Maybe you have formulas
that evaluate to ="". So that they're getting sheets created, too???

For Each vManager In vManagers.Cells
if trim(vmanager.value) = "" then
'do nothing
else
With Workbooks("check deposit report.xls")
.Worksheets("Check-Deposit").Copy _
befo=.Sheets(1)
End With
Set TempWks = ActiveSheet

With TempWks
.Unprotect
.Range("F43").Value = vManager.Offset(0, -3).Value 'date
.Range("b43").Value = vManager.Offset(0, -1).Value 'borrower
If LCase(vManager.Offset(0, 1).Value) = "broker" Then
.Range("f32").Value = "Broker"
Else
.Range("F32").Value = "Retail"
End If
.Range("a43").Value = vManager.Offset(0, 3).Value 'Loan Number
.Range("i27").Value = vManager.Offset(0, 7).Value 'Amount

'protect??
.Protect
End With
end if
Next vManager

John Pierce wrote:

I'm back. That was weird. Therre was this intense light and then . , ,
nothing, till three weeks later.
So where was I?
Dave, I tried your code. It ran and ran and ran, hundreds of pages
when there should have been only about 70 or so. Also, it picked up
all the managers instead of just S. O'Neil.
I would really like to use a dynamic array to do this because I would
like to learn to work with them. Could this program be converted to
that?

--

Dave Peterson

Dave, I tried your code. It ran and ran and ran, hundreds of pages
when there should have been only about 70 or so. Also, it picked up
all the managers instead of just S. O'Neil.
I would really like to use a dynamic array to do this because I would
like to learn to work with them. Could this program be converted to
that?

I thought that you wanted all the cells in that column to have a sheet created.

If you want just that single manager...

For Each vManager In vManagers.Cells
if lcase(vmanager.value) < lcase("S. O'Neil") then
'do nothing
else
With Workbooks("check deposit report.xls")
.Worksheets("Check-Deposit").Copy _
befo=.Sheets(1)
End With
Set TempWks = ActiveSheet

With TempWks
.Unprotect
.Range("F43").Value = vManager.Offset(0, -3).Value 'date
.Range("b43").Value = vManager.Offset(0, -1).Value 'borrower
If LCase(vManager.Offset(0, 1).Value) = "broker" Then
.Range("f32").Value = "Broker"
Else
.Range("F32").Value = "Retail"
End If
.Range("a43").Value = vManager.Offset(0, 3).Value 'Loan Number
.Range("i27").Value = vManager.Offset(0, 7).Value 'Amount

'protect??
.Protect
End With
end if
Next vManager

John Pierce wrote:

Dave, I tried your code. It ran and ran and ran, hundreds of pages
when there should have been only about 70 or so. Also, it picked up
all the managers instead of just S. O'Neil.
I would really like to use a dynamic array to do this because I would
like to learn to work with them. Could this program be converted to
that?

--

Dave Peterson

What goes where it says " 'do nothing"?

Nothing.

I used it as a comment to show that if the name is different from what you want,
then the program doesn't do anything.

John Pierce wrote:

What goes where it says " 'do nothing"?

--

Dave Peterson