I probably meant a visual basic cache but that may be no less dumb! I'm
sorry Kai - your answer is not clear to me, since I'm a novice at amcros.
I don;'t know if you're claiming that the filenames must be saved somewhere
in the macro, but I don't think they are - it was designed to work with
whatever other files were in the same folder. In fact, I just ran the macro
after closing down EXCEL a few times and it didn't exhibit this intermittent
problem - this time. In any event, I know it will happen again and I'd like
to understand it, so here are two of the subs in the macro that are, I
think, responsible. Tell me if you see anything that can cause it to,
somehow, retain some memory of the input filenames last time this output
template was run, even if you go get a fresh output template each time (to
me, this sounds impossible).

Public Sub GetFileList()
sPath = ActiveWorkbook.Path & "\"
sOT = ActiveWorkbook.Name

Set fs = Application.FileSearch
With fs
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
ReDim arrFiles(.FoundFiles.Count - 1)
For d = 1 To .FoundFiles.Count
arrFiles(d - 1) = .FoundFiles(d)
Next
Else
MsgBox "No files found in " & sPath & " or its sub-folders."
End
End If
End With
End Sub

Public Sub ProcessFiles()
Application.ScreenUpdating = False
For d = 0 To UBound(arrFiles)
sFile = arrFiles(d)
GetTheDate

If sFile < ActiveWorkbook.Name Then
Workbooks.Open (arrFiles(d))
Application.StatusBar = "Processing file: " & d + 1 & " - " &
sFile
For s = 1 To ActiveWorkbook.Sheets.Count
Sheets(s).Select
sSheet = ActiveSheet.Name
dRowCount = ActiveSheet.UsedRange.Rows.Count

If sSheet = "lcg" Or sSheet = "LCG" Or sSheet = "lcv" Or
sSheet = "LCV" Or _
sSheet = "mcg" Or sSheet = "MCG" Or sSheet = "mcv" Or
sSheet = "MCV" Or _
sSheet = "scg" Or sSheet = "SCG" Or sSheet = "scv" Or
sSheet = "SCV" Then
GetRowCount
If dRowCount = 4 Then
'transfer A
Range("A4:A" & dRowCount).Select
Selection.Copy
Workbooks(sOT).Activate
Sheets(sSheet).Select
SetSheetCounter
Selection.PasteSpecial Paste:=xlPasteValues
Selection.End(xlDown).Offset(1, 0).Select
dEnd = ActiveCell.Row
'transfer C and D
Workbooks(sFile).Activate
Range("C4:D" & dRowCount).Select
Selection.Copy
Workbooks(sOT).Activate
Sheets(sSheet).Select
Range("C" & dStart).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A" & dStart & ":A" & dEnd - 1).Value =
sDate
Workbooks(sFile).Activate
End If
End If
Next
Application.DisplayAlerts = False
Workbooks(sFile).Close
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

"Kai Uwe Schmidt" wrote in message
...
Hi Dean,

I never heard of an "Excel Cache"(there is only the "Personal Workbook").
It
looks as if your macro uses a fixed file reference. Look for explicit file
names in the 'copy & paste'-area (e.g. "set input file = fso.getfilename
("X:\"...) . To make your macro run, you can either replace file names in
the code or more simply save new input files with the fixedly referenced
names.

Best regards,
Kai



"Dean" schrieb im Newsbeitrag
...
I have a template that basically uses a macro to copy and paste from all
input files that are placed in its same folder and paste that stuff into
itself. It also extracts the dates from the end of each of the filenames.
I ran it with a lot of files there and it, basically, worked. I know that
it does not know the names of the files that will be there, in advance.

Then, I start over with a fresh template and try to run it again with
many of the files removed from the folder, and VB gives me an error
message telling me it can't find some of the removed files. Someone says
it's some sort of caching, apparently. I don't understand. Is there
some way to clear EXCEL caches? How would a fresh copy know of files
that it, presumably, has never seen?

Let me know if I need to show you the macro, which was created by
somebody else. I hope not because it seems that my question is more
basic

Thanks!
Dean