Dynamic Array

[John Pierce]

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 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]

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?

 

[John Pierce]

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]

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

 

[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]

What goes where it says " 'do nothing"?

 

[Dave Peterson]

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]

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.

 

[John Pierce]

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?

 

[Dave Peterson]

I don't think I've ever created this many new worksheets based on copying an
existing sheet.

If I recall correctly, you can close the workbook (and excel???) after creating
several worksheets (before it blows up), then reopen and continue where you left
off.

I _think_ that another workaround may be to use a worksheet in a different
workbook as the template worksheet. I _think_ that people have said that stops
the problem.

 

[John Pierce]

So you've heard of this limit on the number of times a sheet can be
copied? Any idea why 56? I don't know why I hadn't thought of it
sooner but I finally tried to manually copy the template sheet after
running the program and it simply would not allow me to add any more
copied sheets beyond the 57 already there which is consistent with
the program stopping but I could insert a new blank sheet and I could
apparently insert as many as I wanted (I tried up to 16). So it must
be something to do with the Copy method in Excel 2003 is what I am led
to conclude.

On another topic, I'm using some code that I think you posted for
generating a tool bar at run time that closes when the workbook is
closed. I was wondering if such a tool bar could be further restricted
to appear only on one specific worksheet of one specific workbook.
Currently it appears in all open Excel workbooks and sheets until the
host file is closed. It has very specific tools that only work on one
sheet. It isn't really a problem as it is, just a matter of elegance.
Thanks

 

[Dave Peterson]

I _think_ I've read posts with problems with inserting sheets in xl97. But I'm
not sure if it applies to xl2003. Maybe you can google for some hints.

And you could use the workbook_deactivate (under ThisWorkbook) and the
worksheet_deactivate (under the worksheet you want) to delete the toolbar.

And you could use the worksheet_activate (under the worksheet) to rebuild it.
The workbook_activate event (under ThisWorkbook) will have to be smart enough to
check the activesheet name to see if the toolbar should be recreated.

Under ThisWorkbook:

Option Explicit
Private Sub Workbook_Activate()
If LCase(Me.ActiveSheet.Name) = LCase("Sheet1") Then
Call CreateMenu
End If
End Sub
Private Sub Workbook_Deactivate()
Call DeleteMenu
End Sub


Under the sheet that needs the toolbar:

Option Explicit
Private Sub Worksheet_Activate()
Call CreateMenu
End Sub
Private Sub Worksheet_Deactivate()
Call DeleteMenu
End Sub

You could hide the toolbar if you wanted. But then the users could use
tools|customize to show it whenever they want.

(I didn't test the code or compile it, either.)

 

[Dana DeLouis]

Hi. That Excel 97 kb article is no longer around (afaik), but this
article happens to refer to that problem you are thinking of...

http://support.microsoft.com/kb/236299

- - -
Dana DeLouis

 

[Dave Peterson]

That's the one I was thinking of...

Thanks Dana.