copy and paste in different columns with conditions

  • Thread starter Thread starter 71marco71
  • Start date Start date
7

71marco71

Hello guys
I have two sheets. In the first sheet there is a table with dat
contained in five columns (A;B;C;D;E).In the second sheet there i
another table with data contained in five columns. I would like to cop
some cells present in columns A,B,D of the the second sheet in th
colums B,C,D of the first sheet, below the data already present.Th
data to copy in the first sheet are only those which have in column
of the second sheet the text "#D/N".
I attache a file whit the axample

Sorry for my not good english and thanks in advance for your hel

Attachment filename: prova.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=42085
 
Marco
In your file, none of the rows has #D/N in Column D. But Column E has
#N/A in your sample (green) rows. I'll assume you meant Column E for the
#N/A criteria.
The following macro does what you want. Note that the second sheet must be
the active sheet when you run this macro.
If you send me a valid email address for you, I'll send you your
original file with the macro in it. HTH Otto
Sub CopyNA()
Dim ColARng As Range
Application.ScreenUpdating = False
Set ColARng = Range("A1", Cells(Range("A" & Rows.Count). _
End(xlUp).Row, 5))
ColARng.AutoFilter Field:=5, Criteria1:="#N/A"
With Sheets("1")
Range("A2", Range("B" & Rows.Count).End(xlUp)). _
SpecialCells(xlCellTypeVisible).Copy
.Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
Range("D2", Range("D" & Rows.Count).End(xlUp)). _
SpecialCells(xlCellTypeVisible).Copy
.Range("D" & Rows.Count).End(xlUp)(2).PasteSpecial
End With
ColARng.AutoFilter
Range("A1").Select
Application.ScreenUpdating = True
End Sub
 
Back
Top