You may think that you're creating that workbook, but I don't think you are (and
neither does excel!):

I bet if you added a message box:

....
If Counter = 7800 Then
MsgBox "Creating the new workbook!"
Source.Worksheets.Copy
Set Destination = ActiveWorkbook
ActiveSheet.Name = Counter
Else
....

You'd never see that msgbox.

An alternative is to create the workbook first and then just copy the sheets
into that new workbook.

First, I don't know what a .msa file is. Are you sure it's opening correctly?

This has a few msgboxes that may help you find the problem:

Option Explicit
Sub Blah()
Dim Counter As Long
Dim Source As Workbook
Dim Destination As Workbook
Dim R As Range

Const MyDir As String = "c:\PromoTrack\MSA\"

Application.ScreenUpdating = False

Set Destination = Workbooks.Add(1) 'single sheet
Destination.Worksheets(1).Name = "DeleteMeLater"

For Counter = 7800 To 7809
Set Source = Workbooks.Open(MyDir & Counter & ".msa")
Set R = Source.Worksheets(1).Range("B2")
If LCase(Trim(R.Value)) = LCase(Trim("Frozen and Chilled")) Then
'for testing only:
MsgBox "copying: " & Source.FullName

'copy just the first worksheet?
With Destination
Source.Worksheets(1).Copy _
After:=.Worksheets(.Worksheets.Count)
.Worksheets(.Worksheets.Count).Name = Counter
End With
Else
'just for testing
MsgBox "Not copying: " & Source.FullName
End If
Source.Close savechanges:=False
Next Counter

If Destination.Worksheets.Count = 1 Then
'only that dummy sheet is there
MsgBox "Nothing was copied"
Destination.Close savechanges:=False
Else
Application.DisplayAlerts = False
Destination.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True
Destination.SaveAs MyDir & "Summary.xls"
MsgBox "Frozen MSAs compiled and saved as: " & Destination.FullName
End If

Application.ScreenUpdating = True

End Sub



Petitboeuf wrote:

.. Destination = Nothing.

So why does it not create/keep the workbook as previously set?

Frozen and Chilled is in 5 of the 8 workbooks that I open, including
number 7800...


Very confused.....

--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=535960

--

Dave Peterson