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")
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("Somesheetnamehere")
'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 _
before:=.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")
> 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?

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 _
before:=.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

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 _
before:=.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

Dave, Thanks for hangin in there. It works but there's a problem.
After running 56 iterations it stops with Run-time error '1004' Copy
method of Worksheet class failed. The earlier version did exactly the
same thing which is why I was trying to change it somehow. I am
running it on machines with Windows XP and Excel 2003.
When I try to run it on a new computer with Vista and Excel 2007 I get
an error at LCase, Compile error, Can't find project or library.

I figured out the Can't find project or library error. The file had a
reference to Personal.xls but the new computer is so new I haven't
even started my macro book. I simply deleted the reference from the
file and the code ran fine. I tested it with 146 iterations.
Unfortunately, I have to run it on machines at work that have XP/2003
and where it will only run 56 times. The problem appears to be the
Excel version rather than the program, don't ya think?