Copy formulas, not values

[Fred Smith]

I activate a filter, then use the following to copy the filtered records to
a new workbook:

Selection.CurrentRegion.Copy
Workbooks.Add
ActiveSheet.Paste

However, this copies values, not formulas. If I copy each row separately, I
get the formulas, but not when I do it en mass as above.

How can I get the formulas copied, rather than the values?

 

[Debra Dalgleish]

Instead of filtering, you could mark the records that meet your
criteria. Then sort the list, copy and paste the contiguous range, and
clear the marks.

There's an example here, that copies the results to a new workbook:

http://www.contextures.com/excelfiles.html

Under Functions, look for 'Extract Items with Formulas'

 

[Tom Ogilvy]

This seemed to work for me:

Sub CopyFormulas()
Selection.CurrentRegion.Copy
Workbooks.Add
ActiveSheet.Range("A1").PasteSpecial xlFormulas
ActiveSheet.Range("A1").PasteSpecial xlFormats
End Sub


Tested in xl97.