Copying worksheets into one workbook... based on content

P

Petitboeuf

Dear all

I have 9000 sheets + into a folder and I would like to compile them al
into different workbooks based on the content of cell B2...

So far I have the following code (which works great) but I do not kno
how to do multiple IF... or IF within IF... :eek:


Code
-------------------

Sub Compile()

Dim Counter As Long
Dim Source As Workbook
Dim Destination As Workbook

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

Application.ScreenUpdating = False

For Counter = 1 To 9999
Set Source = Workbooks.Open(MyDir & Counter & ".msa")
If Counter = 1 Then
Source.Worksheets.Copy
Set Destination = ActiveWorkbook
ActiveSheet.Name = Counter
Else
Source.Worksheets.Copy After:=Destination.Worksheets(Destination.Worksheets.Count)
Destination.Worksheets(Destination.Worksheets.Count).Name = Counter
End If
Source.Close False
Next

Destination.SaveAs MyDir & "Compilation.xls"

Application.ScreenUpdating = True

MsgBox "Done"

End Sub

-------------------


Where and how would I tell it to check the text content of cell B2 i
each of the sheets and if text=blue then copy else do nothing??

Many thanks for sharing your expertise!
 
Top