Does Excel 2003's DO Until LOOP have a buffer?
Using VBA, I am opening a master file and then loop through 77 more files to
copy a sheet to the master file. All works without Excel throwing a dialog
asking to save changes until the 73 file. I can move the 73rd file up in to
the loop and Excel does not throw the dialog. This tells me there is nothing
wrong with the file. Any help would be appreciated.

This is the code:
Sub Combine_Files()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

w = ActiveCell.Value
ww = ActiveCell.Offset(0, -1).Value
a = Range("period").Value
F = Range("formatting").Value
vv = Application.Sheets.Parent.Name

dir_select = "c:\peoplesoft nvision reports\2009\oney group\" & a & "\"
Workbooks.Open Filename:=dir_select & w

Sheets("P&L").Select
Sheets("P&L").Name = ww

Windows("oney files to sups").Activate
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value = ""
z = ActiveCell.Address
x = ActiveCell.Value
xx = ActiveCell.Offset(0, -1).Value
cc = ActiveCell.Offset(0, -2).Value

dir_select = "c:\peoplesoft nvision reports\2009\oney group\" & a & "\"
Workbooks.Open Filename:=dir_select & x

Sheets("P&L").Select
ActiveSheet.Select

ActiveSheet.Copy Befo=Workbooks(w).Sheets(ww)
ActiveWorkbook.Sheets("P&L").Tab.ColorIndex = cc
Sheets("P&L").Name = xx

If F = "LIZ" Then
ActiveSheet.Outline.ShowLevels RowLevels:=1
End If

Workbooks(x).Close SaveChanges:=False

Windows("oney files to sups.xls").Activate

Range(z).Select
ActiveCell.Offset(1, 0).Select
Loop

Workbooks(w).Activate
Sheets(ww).Select

Application.ScreenUpdating = True

msg = "Do not pass go, do not collect $200"
MsgBox msg & Chr(13) & "until you save this file!"
End Sub

I don't think so... Ultimately all software applications have physical limits
based on your software configuration and system memory but looping through 77
files is well within the limits of most of the present day systems.

Does it still throw the warning after 73 files even if you move the offendng
file up?

Just to complete your task, you may save after processing each file and find
out the cause later :-)

"Dan" wrote:

Does Excel 2003's DO Until LOOP have a buffer?
Using VBA, I am opening a master file and then loop through 77 more files to
copy a sheet to the master file. All works without Excel throwing a dialog
asking to save changes until the 73 file. I can move the 73rd file up in to
the loop and Excel does not throw the dialog. This tells me there is nothing
wrong with the file. Any help would be appreciated.

This is the code:
Sub Combine_Files()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

w = ActiveCell.Value
ww = ActiveCell.Offset(0, -1).Value
a = Range("period").Value
F = Range("formatting").Value
vv = Application.Sheets.Parent.Name

dir_select = "c:\peoplesoft nvision reports\2009\oney group\" & a & "\"
Workbooks.Open Filename:=dir_select & w

Sheets("P&L").Select
Sheets("P&L").Name = ww

Windows("oney files to sups").Activate
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value = ""
z = ActiveCell.Address
x = ActiveCell.Value
xx = ActiveCell.Offset(0, -1).Value
cc = ActiveCell.Offset(0, -2).Value

dir_select = "c:\peoplesoft nvision reports\2009\oney group\" & a & "\"
Workbooks.Open Filename:=dir_select & x

Sheets("P&L").Select
ActiveSheet.Select

ActiveSheet.Copy Befo=Workbooks(w).Sheets(ww)
ActiveWorkbook.Sheets("P&L").Tab.ColorIndex = cc
Sheets("P&L").Name = xx

If F = "LIZ" Then
ActiveSheet.Outline.ShowLevels RowLevels:=1
End If

Workbooks(x).Close SaveChanges:=False

Windows("oney files to sups.xls").Activate

Range(z).Select
ActiveCell.Offset(1, 0).Select
Loop

Workbooks(w).Activate
Sheets(ww).Select

Application.ScreenUpdating = True

msg = "Do not pass go, do not collect $200"
MsgBox msg & Chr(13) & "until you save this file!"
End Sub