The old code was/is working fine, so the problem is in the new code. I
fixed the wrap around problem as you advised. I am a little confused by the
two arguments needed for "Consolidate". When you call it from Consolidate
All, it seems that you may be reversing their order from what Consolidate
wants. Is that correct?

Considering the above, I am also a bit confused by your suggestion fro
automating the assumed filename, perhaps because you have copied the routine
to name the project sheet. Please assume that both the filename and the
worksheet name are sequential. Let's say the filenames are test1 thru test60
and that the sheet names will be joe jones test1 thru joe jones test 60

Lastly, though it is not essential info for me to understand, I know that a
worksheet called Utility somehow captures the sheet names, but am confused
as to how it gets them. There are statements such as

If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else

in the macro which are probably doing it, but I think I am confused because
I am still interpreting them backwards. When I see "Then B = A", I assume
that it means to assign A's value to B. But I am guessing that this is just
the opposite. If so, I guess you have answered my question. If not, then
if you have any clue as to how it is finding its way onto the Utility
worksheet, let me know (it may be off somewhere in another macro). I do
know which cell in each input file becomes Prj_Sht. I just am a little
confused as to where the macro puts it.

Can you kindly answer these questions and rewrite Consolidate ALL so that it
automates both the filename and the worksheet name?

Thanks much for your help!
Dean

"Joel" wrote in message
...
Dean: I trying to minimize the risk of breaking the code that is already
working. I don't think you have posted all the code which I don't need
but
don't want to break. There is probably code on the sheet pages and
Thisworkbook that you are not aware of. Lets not worry about that code.
Lets just get your test function working. From your psting I'm not sure
if
the old code still works or the problem is the new code. right now I'm
assuming the new code had problems.


I modified my code to include the path

Sub Consolidate_All()
mypath = "C:\Documents and Settings\Dean\My Documents\AW\CONSOL
TEMPLATE\files\"

Dim n As Integer
ProjectFile = Array("","Filename1","Filename2",.........)
For n = 1 To 60
ProjectName = Range("projectName" & (n)).Value
Call Consolidate(ProjectName, mypath & ProjectFile(n))
Next n
End Sub

Answer to questions
--------------------------------------------------------------------------------------------
1) in the array, do the filenames need the .xls extension?
Yes
2) if the names of
the file really were test1 thru test60, would the array statement need
them
all listed or is it smart enough for a ... to work?

The are lots of ways to get the filename. The array method will work
ynder
any condition. If there is a pattern to the file name then the array may
not
be necessary such like the code below

For n = 1 To 60
ProjectName = Range("projectName" & (n)).Value
Call Consolidate(ProjectName, mypath & "test" & n)
Next n

3) By path, I assume you
mean the path to the folder where the files will be. If so, I think we
can
ignore that for now, i.e., as long as the last time it was queried, we
supplied the same folder, it will use that folder as a default, right?

The new code I gave you now includes the path

4) When you say:

I will make the ProjectFile Array easier to enter the names (they will be
shorter).

I don't know what you mean. Are you planning on doing some more work?

As I said before, There are lots of ways to get the files names depending
if
there is a pattern to the names or if they are random. I wanted yo to see
what I was doing and then expected exactly the questions you are asking.

the additional work is what I'm doping now which is helping you find the
best way to get the filenames. I hope I'm not confusing you. I felt it
was
better not to give you too much information until you saw the code. the I
planed to answer the questions you needed answered.

5) I tried copying your macro into my file, just using, test1, test2, and
test3
as the three project names in the array and I notice that, every time the

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

command shows up, it is in red font. What could be the problem there?

I t looked like the line wrapped. The underscore "_" should be on the
same
line as the = and False should be on the next line. the posting sometimes
get extra lines if the lines are too long. I did not modify these lines.
Look at your original code and enter these lines like they were originally
enetered.

6)
Also, in red font is these two msgbox commands:

MsgBox ("The selected project was successfully consolidated."),

MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical

I did not modify these lines. It looks like the lines wrapped and should
be
one line. Look at the original code and enter these lines like they
appeared
in the original code.
--------------------------------------------------------------------------------------------
"Dean" wrote:

I think I figured out that you meant "It", not "I", below, Can you
modify
the code to use a path? Assume the path is:

C:\Documents and Settings\Dean\My Documents\AW\CONSOL TEMPLATE\files

Dean

"Dean" wrote in message
...
Unfortunately, I am not too swift with macros, so I'm a tad confused.

Within the array, do the filenames need the .xls extension? If the
names
of the file really were test1 thru test60, would the array statement
need
them all listed or is it smart enough for a ... to work? By path, I
assume you mean the path to the folder where the files will be. If so,
I
think we can ignore that for now, i.e., as long as the last time it
was
queried, we supplied the same folder, it will use that folder as a
default, right?

When you say:

I will make the ProjectFile Array easier to enter the names (they will
be
shorter).

I don't know what you mean. Are you planning on doing some more work?

I tried copying your macro into my file, just using, test1, test2, and
test3 as the three project names in the array and I notice that, every
time the

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

command shows up, it is in red font. What could be the problem there?

Also, in red font is these two msgbox commands:

MsgBox ("The selected project was successfully consolidated."),

MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical

Thanks!
Dean

"Joel" wrote in message
...
Dean: I split Consololidate Project into two subroutines. Just moved
around
a little bit of the code and created a new subroutine consolidate. My
plan
is to just call consolidate for testing.

You will need a new routine that will look something like
Consolidate_All.
Notice I put a blank string at the beginning of the Array to skip the
first
array member which has an index of zero.

I think you may want to pass the new function the Path as a third
parameter.
I will make the ProjectFile Array easier to enter the names (they will
be
shorter).

Let me know if you need additional help.

Sub Consolidate_All()
Dim n As Integer
ProjectFile = Array("","Filename1","Filename2",.........)
For n = 1 To 60
ProjectName = Range("projectName" & (n)).Value
Call Consolidate(ProjectName,ProjectFile(n))
Next n
End Sub



Sub Consolidate_Project()
Dim i As Integer
Dim t As Integer
Dim n As Integer
Application.EnableEvents = False
Application.ScreenUpdating = False

'
'Check to see if a file was selected
'
If FileName = "" Then
MsgBox ("Please select a project file to consolidate."), vbCritical
Exit Sub
Else
End If
'
'Check to see if a project location was selected
'
t = 0
For i = 1 To 60
If Consolidate_Dialog.Controls("ProjectButton" & (i)).Value = True
Then
t = t + 1
Else
End If
Next i
If t = 0 Then
MsgBox ("Please select a project location to consolidate to."),
vbCritical
Exit Sub
End If
'
' Use the selected option button
'
n = 1
Do Until Consolidate_Dialog.Controls("ProjectButton" & (n)).Value =
True
n = n + 1
Loop
'
'Select the project to consolidate to.
'
If Sheets("Utility").Range("I" & (n + 40)) "" Then
Prj_Sht = Sheets("Utility").Range("I" & (n + 40))
Else
Prj_Sht = Sheets("Utility").Range("G" & (n + 40))
End If

call Consolidate(FileName, Prj_Sht)
end sub


Sub Consolidate(ByVal FileName as String, ByVal Prj_Sht as String)
'
'Perform the Consolidation
'
'Get the Project Worksheet ready for import.
Sheets(Prj_Sht).Activate
'ActiveSheet.Range("A1").ClearContents' changed by DA
ActiveSheet.Range("A1:CN230").ClearContents 'changed by DA

ConFile = FileNameOnly(FileName)
Workbooks.Open (FileName)
If SheetExists("Output") = True Then
'Import Project Name
Sheets("Plan Input").Select
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False

'added by DA on 9/7/06 to paste names into worksheet
Sheets("Names").Select
ActiveSheet.Range("G3").Select
ActiveCell.Offset(0, n).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

'end of DA edit

'Import All Data by DA
Workbooks(ConFile).Activate
Sheets("Output").Select
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveSheet.Range("A2:CN800").Select ' changed by DA in oct 2007
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
ActiveWorkbook.Sheets(Prj_Sht).Select
ActiveSheet.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'lots taken out here by DA that brought in sections one-by-one

'Clear Clipboard
Workbooks(ConFile).Activate
Sheets("Output").Select
ActiveSheet.Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
'
'Close the source workbook
'
ActiveWorkbook.Close False
'
'Change project sheet name and clear Consolidate Dialog
'
ActiveWorkbook.Sheets(Prj_Sht).Select
Call Park
ActiveSheet.Name = Range("ProjectName" & (n))
Sheets("Utility").Range("I" & (n + 40)) = Range("Projectname" &
(n))
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Consolidate_Dialog.Controls("ProjectButton" & (n)) = False
Sheets("Names").Select
Range("A1").Select
MsgBox ("The selected project was successfully consolidated."),
vbInformation
Else
MsgBox ("The workbook you selected is not a valid business plan
model."), vbCritical
ActiveWorkbook.Close False
End If
n = 0
FileName = ""
ConFileName = ""
Application.EnableEvents = True
End Sub

"Dean" wrote:

Thanks so much for your help. I'm not sure I know everything that
goes
on
behind the scenes, so I may err on the side of giving you more than
you
need. The first subroutine loads the dialog screen. From looking at
the
form, this seems to simply populate the labels beside the 60 buttons.
I
have a hunch you don't need it but here that is:

Sub Load_Consolidate_Dialog()
'If this bombs, you may have to clear out the names in the hidden
"utility"
worksheet
Dim n As Integer
For n = 1 To 60
If Range("projectName" & (n)).Value "" Then
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= Range("ProjectName" & (n)).Value
Else
Consolidate_Dialog.Controls("ProjectButton" & (n)).Caption _
= "Project " & (n)
End If
Next n
Consolidate_Dialog.Show
End Sub

It has several buttons on it, such as clear selected projects but I
don't
use them, so I only care about the two buttons I mentioned.

From what you said, I assume that the select file button invokes this
macro
(but I may be mistaken - it's always an EXCEL file):

Function GetOpenFile(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If

' Define the filter string and allocate space in the "c"
' string Duplicate this line with changes as necessary for
' more file templates.
strFilter = ahtAddFilterItem(strFilter, _
"Access (*.mdb)", "*.MDB;*.MDA")
' Now actually call to get the file name.
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
GetOpenFile = varFileName
End Function

Lastly, the consolidate button uses this macro (which may be a
nightmare
to
follow, as even I have had my hand in adding to it):

Sub Consolidate_Project()
Dim i As Integer