Replies

This should work regardless of whether the referenced workbook is open:

=’C:WindowsDesktop[test data.xls]Sheet1′!A1

You have to be careful to get the ‘ ‘ and [ ] in the right place – I always end up having to look it up (John Walkenbach’s Excel 2000 Bible, which I wholeheartedly recommend).

HTH

Graeme

Lonnie,
This is one way, if I’ve understood you correctly:

The items are in cells A1:A20 and the ‘yes’s are in cells B1:B20; Sheet2 contains a title for the new list in cell A1

Sub CopyItems()

Dim cl As Range

For Each cl In Range(“A1:A20”)
If cl.Offset(,1).Value = “yes” Then
cl.Copy Destination:=Sheet2.Range(“A65536”).End(xlUp).Offset(1)
End If
Next

End Sub

As ever, I’m sure there are more elegant ways!

HTH

graeme

You can use this to exit the current sub:

If endcondition then
Exit Sub
End If

There is also the End statement, which stops execution immediately and therefore can make an almighty mess if you’re not careful to clean up before using it. In fact, best not to use it at all!

BTW drkrealm has an excellent spreadsheet with links to all sorts of tutorials; I think he posted it recently.

HTH

graeme

If your range has no spaces you can use Range(“A6”).CurrentRegion.Select. Also, if you give the range a name then depending on how your data is refreshed it will expand automatically.

HTH

Graeme