Copy autofilter row by row

[Guest]

Hi,
I made autofilter on range in sheet1 and I need to copy it (only the rows
after the filter) for another range in sheet2 but I need to do it row by row
because for each row in column 3 i need the value from col 5.

For example:
Sheet1:
1,5,aa,33,bbb
2,6,bb,45,ccc

Sheet2: (after the copy)
1,5,bbb,33,bbb
2,6,ccc,45,ccc

How can I do it?

thanks,
Shlomit

 

[Dave Peterson]

It looks like you could copy|Paste the visible cells in the autofilter range,
then come back to copy|paste the 5th column over the 3rd column.

If that's something you want to try:

Option Explicit
Sub testme()

Dim myRng As Range
Dim DestCell As Range

With Worksheets("sheet2")
'next open cell in column A???
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Worksheets("sheet1")
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
MsgBox "nothing visible in the filter!"
Exit Sub
End If
Set myRng = .Resize(.Rows.Count - 1, .Columns.Count) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
myRng.Copy _
Destination:=DestCell

myRng.Columns(5).Copy _
Destination:=DestCell.Offset(0, 2)
End With
End With

End Sub

I didn't know where to paste, so I just used the next available cell in column
A.