I'm glad you found the posting. If you need information on connecting
directly to the Microsoft servers, see Tushar Mehta's site:

http://www.tushar-mehta.com/misc_tut..._ng/index.html

wrote:
Hi Debra,

That is so cool! Thanks very much for taking the time to
do that. I'm now going to spend the rest of the day
looking very happy

Best Regards,

CalumMurdo Kennedy
p.s. For some reason your message wasn't picked up by
Freeserve (my ISP) so I guess I'll have to stick to using
the Microsoft forums directly.



-----Original Message-----
Instead of filtering, you could mark the records that

meet the criteria,

sort by the column that contains the mark, and copy the

results to a

new sheet. This will keep the formulas intact. (You can

do this

manually, or

For example, type your first criterion in cell M1. In the

first blank

column to the right of the table, enter a formula that

checks each row's

data against the criterion: =IF(D2=$M$1,TRUE,"")

Copy the formula down, then sort this column in

descending order.

Copy the headings and marked rows (excluding the column

with the TRUE),

and paste into a new workbook.

I've added a sample workbook to my site:
http://www.contextures.com/excelfiles.html#Function

under the subheading 'Extract Items with Formulas'

It has a named range (NameList) that contains the

criteria, and uses the

following code to copy the data to a new workbook:

'========================
Sub CopyToWorkbook()
Dim c As Range
Dim rng As Range
Dim r As Long
r = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Application.DisplayAlerts = False
For Each c In Range("NameList")
Range("M1").Value = c.Value
Range("J2").Formula = "=IF(D2=$M$1,TRUE,"""")"
Range("J2").AutoFill Destination:=Range("J2:J" & r)
Range("A1:J38").Sort Key1:=Range("J2"),

Order1:=xlDescending, _

Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Columns("J:J").Copy
Columns("J:J").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False,

Transpose:=False

Set rng = Columns("J:J").SpecialCells

(xlCellTypeConstants, 4)

rng.Offset(-1, -9).Resize(rng.Rows.Count + 1, 9).Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="c:\" & c.Value

& ".xls"

ActiveWorkbook.Close
Next c
Columns("J:J").Clear
Range("M1").Clear
Application.DisplayAlerts = False
End Sub
'===========================

CalumMurdo Kennedy wrote:

Hi Guys,

I've got a problem that I can't see an obviously easy

answer too

(although I'm sure you'll prove me wrong!). I have a

large

spreadsheet that I want to break down using the drop

down list

feature. I need each file to show the contents of the

drop down list

for that option only.

This I can do manually which is ok (albeit it REAL

slow), however, the

problem is that when I copy and paste the selected

items into a new

worksheet the formulas are all converted to values.

Does anyone have any idas of how I can get around this

(or at least

speed the process up)?

Any help is greatly appreciated.

Best Regards,

CalumMurdo Kennedy
GB-1-1563
www.taekwondo.freeserve.co.uk


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html