Filtering Problem

[Jeff Etcell]

I have a spreadsheet that is constantly being filtered, it
contains our item codes and the vendors associated to
them, along with many columns of information.

My problem which I hope can be eased easily is that whilst
my spreadsheet is filtered, I create a formula within the
filter, but I wish to copy and paste special, values but
it doesnt paste into the filtered cells but into cells
asif it wasnt filtered.

Can this be overcome easily without having to sort by
column H (for instance) then action performed, then
resorted back to Column A?

Thanks to all who offer their advice

Jeff

 

[JulieD]

Hi Jeff

if i'm understanding you correctly, before you copy choose edit / goto /
special - visible cells only and now copy & paste special.

Let us know if this solves your problem.

Cheers
JulieD

 

[Dave Peterson]

If I understand correctly, you're filtering a range. Then typing a formula in
one of those visible cells and dragging down the column.

Then you want to copy|paste special|values to those formulas???

If yes, how about a macro.

Just select your range to convert and run this:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myArea As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no formulas found in selection"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Value = .Value
End With
Next myArea

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(But I'm not quite sure I read your requirements correctly.)