Copying worksheets into one workbook... based on content
 

Dear all

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

So far I have the following code (which works great) but I do not know
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.Workshee ts.Count)
Destination.Worksheets(Destination.Worksheets.Coun t).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 in
each of the sheets and if text=blue then copy else do nothing??

Many thanks for sharing your expertise!!


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