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 data
contained in five columns (A;B;C;D;E).In the second sheet there is
another table with data contained in five columns. I would like to copy
some cells present in columns A,B,D of the the second sheet in the
colums B,C,D of the first sheet, below the data already present.The
data to copy in the first sheet are only those which have in column E
of the second sheet the text "#D/N".
I attache a file whit the example (in the file there are few rows..but
the macro should run independently of number of rows)
Thanks in advance for your help
Bye

Attachment filename: prova1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=426940
 
I didn't, and won't, open your workbook, but based on your description
this may work for you:


Public Sub CopyDNs()
Dim rDest As Range
Application.ScreenUpdating = False
Set rDest = Sheets("Sheet1").Range( _
"B" & Rows.Count).End(xlUp).Offset(1, 0)
With Sheets("Sheet2")
.Range("A:E").AutoFilter _
Field:=5, _
Criteria1:="#D/N"
On Error Resume Next
With .Range("A2:D" & .Range("E" & _
Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
.Copy Destination:=rDest
rDest.Offset(0, 2).Resize(.Count, 1).Delete _
Shift:=xlToLeft
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
 
Back
Top