Copy autofiltered column into another column within the same table

[ekacan]

Hi
I am able to autofilter the table with a criteria on Column C, but I can't figure out how to mirror the visible cells on column C, on column B. Is that possible to do?

Example

Col A	Col B	Col C
SN0001	1/31/2013	1/15/2013
SN0002	1/22/2013	2/28/2013
SN0003		3/30/2013
SN0004		4/18/2013

<TBODY>
</TBODY>

I want to filter column C to show only February and March dates, and overwrite the corresponding rows in Col B with the same dates in Col C.
This is a simplified version of what I am trying to do. In reality The column B information overwritten periodically, then further updated with information collected from several different sources.

I appreciate any help you can give me here.

 

[Andrew Poulsom]

After filtering select the cells in column B. In the formula bar type =, point to the cell to the right of the active cell and press Ctrl+Enter. Remove the filter and convert the formulas to values if you want.

 

[ekacan]

Thank you for that info Andrew. However, I failed to mention that I wanted to acomplish that with a VBA set of code. I am wondering if there is a way to collectively mirror autofiltered cells in a column, in a different column in the same table. Maybe I need to forget about using the autofilter function altogether, and do a loop statement for each row. Given that there are several thousand rows, that just doesn't seem like an efficient way to do it.
In other words, I was attemting to accomplish the code below using the Autofilter and copy the filtered cells onto correspoinding cells on Column B.

EndRow=Range("A1").End(xlDown).Row
For i=2 to EndRow
if Cells(i, 3)<> "#N/A" then
Cells(i, 2).value = Cells(i, 3).value
Next i

 

[Andrew Poulsom]

You can use SpecialCells(xlCellTypeVisible) to return only the visible cells.

 

[ekacan]

Thank you. That worked. My actual work needed column D to reflect the value in column I for values other than "#N/A".

Range("A1:J1").Select
Selection.AutoFilter Field:=9, Criteria1:="<>#N/A", Visibledropdown:=True
Range("D1").End(xlDown).Select
ActiveCell.Value = "=" & ActiveCell.Offset(0, 5).Address(RowAbsolute:=False, _
ColumnAbsolute:=False)
ActiveCell.Copy
With Range("D2:D" & EndRow)
.SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteFormulas)
.SpecialCells(xlCellTypeVisible).Font.Color = RGB(0, 0, 255)
End With