Filter Cut Paste

[Apple2_1979]

Hi All,

I need a VBA solution. I need to filter column AL and paste the results to column AK. So far I have this,

Sub Macro7()
'
' Macro7 Macro
'
'
With ActiveSheet.Range("AL2:AL1859")
.AutoFilter Field:=38, Criteria1:= _
"=*isolat*", Operator:=xlAnd, Criteria2:="<>*isolation*"
On Error Resume Next
.SpecialCells(xlCellTypeVisible).Cut Range("AK2")
On Error GoTo 0

End With

End Sub

But it stops at the filter and does not cut then paste. Thank-you in advance.

 

[Snakehips]

Apple2_1979,

Try the following.
The Copy and ClearContents will only act on the visible filtered data.


Sub Macro7()
'
' Macro7 Macro
'
'
With ActiveSheet.Range("AL2:AL1859")
.AutoFilter Field:=38, Criteria1:= _
"=*isolat*", Operator:=xlAnd, Criteria2:="<>*isolation*"
On Error Resume Next

.Copy Destination:=Range("AK2")
.ClearContents
On Error GoTo 0

End With

End Sub

[/CODE]
Hope that helps.

 

[Apple2_1979]

Thanks Snakehips, but...I need the filtered cut data pasted to the cell directly beside the cell it is comming from.
ie.
AL13 to AK13
AL15 to AL15
AL1855 to AK1855


etc...

Thanks for the quick response....cheers!

 

[Snakehips]

Apple2_1979,

Try this. It will do what you want but might be sluggish on a large data set.

Sub Macro7()
'
' Macro7 Macro
'
'
With ActiveSheet.Range("AL2:AL1859")
.AutoFilter Field:=38, Criteria1:= _
"=*isolat*", Operator:=xlAnd, Criteria2:="<>*isolation*"
On Error Resume Next
[COLOR=blue]Set Rng = .SpecialCells(xlCellTypeVisible)
For Each Cell In Rng
Cell.Offset(0, 1).Value = Cell.Value
Cell.ClearContents
Next Cell[/COLOR]
On Error GoTo 0

End With

End Sub

 

[Apple2_1979]

Hi Tony,

I really appreciated your assistance yesterday. You were quick, knowledgable and seemed understanding.

Thank-you again,

Phil (Apple2_1979)