Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"ibbm" <ibbm@discussions.microsoft.com>
wrote in message...
I have a master spreadsheet in which I search through a subdirectory to
update it with monthly data.
This works fine until I try to do an if statement that  checks for the name
of the worksheet in the workbook it is currently reading..  Here is the code
below.  I thank you in advance for your help.  When I run this I get **WRONG
for all of them.

   Application.ScreenUpdating = False
   Set FS = Application.FileSearch
   strPath = "x:\By Store Number"
   szSummary = ActiveWorkbook.Name
   strMonth = "Feb"
   strMonth1 = "Canada February"
   Sheets(1).Name = strMonth1
   strMonth2 = "USA February"
   Sheets(2).Name = strMonth2
   strOther = "Country not Defined"
   Sheets(3).Name = strOther    
   n = 1
   
   With FS
       .NewSearch
       .LookIn = strPath
       .SearchSubFolders = True
       '.FileType = msoFileTypeExcelWorkbooks
       .Filename = strMonth
       iCount = .Execute
       strMessage = Format(iCount, "0 ""Files Found""")
       
       For Each vaFileName In .FoundFiles
             'MsgBox strMessage
           'strMessage = strMessage & vbCr & vaFileName
             Set wb = Workbooks.Open(vaFileName)                            
             ' get info of this workbook to update the summary sales figures              
             Workbooks(szSummary).Activate
             'If worksheet has an information sheet then it's valid else flag it
             For Each ws In wb.Worksheets
               If ws.Name = "Information" Then
                   ThisWorkbook.Sheets(1).Cells(n, 1) = wb.Sheets("Information").Cells(5, 2)
                   ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets("Information").Cells(4, 2)
               Else
                   ThisWorkbook.Sheets(1).Cells(n, 1) = "**WRONG"
                   ThisWorkbook.Sheets(1).Cells(n, 2) = wb.Sheets(2).Cells(3, 2)
               End If
               Next ws
             ThisWorkbook.Sheets(1).Cells(n, 3) = wb.Sheets("Royalty Report").Cells(13, 2)
             ThisWorkbook.Sheets(1).Cells(n, 4) = wb.Sheets("Royalty Report").Cells(13, 3)