On 8 Nov, 14:03, "Jon Peltier" wrote:
And what does this errant code look like?

- Jon

The problem is this:

I have a folder with a number of files; each file has 3 tabs: source
data, detailed analysis and summary analysis. The number of files
varies from 2 to 40, depending on the situations.

My macro processes all files in the folder and merges the analysis
tabs into another file. For example, if there are 20 files, the macro
creates one file "summary.xls" with 20 tabs, one per file, and does
the same with the detailed analyses, copying them to "details.xls".
Only values are copied, not formulas, as source data is not copied.

To do the above, I wrote the sub MyMerge(), which in turn calls the
sub CopyTabVakuesToOtherFile().

If I run MyMerge on the summary analysis, it always works. If I run it
on the detailed analysis, it works in Excel 2003 but crashes in Excel
2007, always at different points in the code.
The code is:

Sub MyMerge(TargetFile, MyFolder, MyTabName, MyPath)
'TargetFile: the file to be created, where tabs will be copied
'MyFolder: the folder containing the files to process (from which to
copy)
'MyTabName: the name of the tab - worksheet - from which to copy
'Mypath: the path of the workbook contaning the macro


'for some misterious reason, this code works fine in Excel 2003 but
often crashes Excel 2007
'no error message is given, nor can I find any pattern in the
behaviour of the softwa
'sometimes it crashes, sometimes it doesn't, and when it does, it
always does at different points in the code

'I added very detailed descriptions in the statusbar so as to
precisely identify
'what steps have been completed

' I only want 1 tab in the new workbooks this macro will create
Application.SheetsInNewWorkbook = 1

'creates the file where the reports will be merged
'and renames the worksheet to "ToBeDeleted" because the 1st, empty tab
will not be needed and we'll delete it
'at the end of the whole process
Workbooks.Add
ActiveSheet.Name = "ToBeDeleted"
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & TargetFile,
CreateBackup:=False, FileFormat:=xlExcel8

thefile = Dir(MyFolder & "\")

FileNumber = 0
'we must specify a variable thefile=dir and then set the while
condition on it, non directly on dir
'otherwise dir would be called more times than needed
Do While thefile < ""

'full path of the file being analyzed in the strats folder
thefilepath = MyFolder & "\" & thefile

'counter to update the status bar showing the progress
'since this is a do... loop (and not a for ... next) we don't know
how many files are in the path
'before running the whole cycle
FileNumber = FileNumber + 1
Application.StatusBar = "Processing file " & FileNumber

'opens the file with the data for segment currently being analyzed
Workbooks.Open (thefilepath)
CurrentlyOpenFile = ActiveWorkbook.Name

Application.StatusBar = "Processing file " & FileNumber & " - file
opened"

Call CopyTabValuesToOtherFile(MyTabName, TargetFile, thefile)

Application.StatusBar = "Processing file " & FileNumber & " - tab
copied to the target file"

'closes the file with the segment data
Workbooks(CurrentlyOpenFile).Close saveChanges:=False
Application.StatusBar = "Processing file " & FileNumber & " -
source file closed"

Workbooks(TargetFile).Save
Application.StatusBar = "Processing file " & FileNumber & " -
target file saved"

'searches the folder again to check if there is any other file
thefile = Dir()
Application.StatusBar = "Processing file " & FileNumber & " - end
of loop"
Loop

Application.StatusBar = "All files processed; almost done"
Call WaitSeconds(Wait)


Application.DisplayAlerts = False
Workbooks(TargetFile).Sheets("ToBeDeleted").Delete
Application.DisplayAlerts = True

Application.StatusBar = "Blank worksheet deleted"


Workbooks(TargetFile).Save


Application.StatusBar = "File saved"


Workbooks(TargetFile).Close

End Sub


Sub CopyTabValuesToOtherFile(SourceTab, TargetFile, TargetTab)
'copies SourceTab to TargetTab in TargetFile
'pasting values only

'stores the name of the currently active file
MyActiveFile = ActiveWorkbook.Name
Sheets(SourceTab).Copy Befo=Workbooks(TargetFile).Sheets(1)
Workbooks(TargetFile).Activate
Sheets(SourceTab).Select
Cells.Select
Selection.Copy

'pastes values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'clears the clipboard; this frees memory and makes sure the macro
is not interrumpted by the message asking
'whether we want to clear the clipboard or not
Application.CutCopyMode = False

'renames the recently copied tab
'trims the text to the first 30 characters
Sheets(SourceTab).Name = Left(TargetTab, 30)

'deletes named ranges in the file
'Necessary as all tabs have the same named ranges
For Each varname In ActiveWorkbook.Names
varname.Delete
Next

'reactivates the file which was active before calling this sub
Workbooks(MyActiveFile).Activate


End Sub

Any help would be enormously appreciated as this is driving me nuts!